Excel tutorials

Extract Numbers from Text and Add Zero in Front of Single-digit Number

Excel program uses a lot of Excel functions. Each function has a syntax and contains arguments divided by a delimiter or separator. To know how to neste a formula within the formula, necessary to know the arguments in the syntax of some Excel functions.

In this Excel tutorial, I'll try to show you how to add a zero digit in front of a one-digit number. The numbers are in the text of one cell. Between numbers is slash right. Notice the picture below the situation of our problem.

In the image below we have the source data in the 'A' column. Cells are formatted as text. As you can see, there are three groups of numbers that are dispensed by a slash. Each group can contain one or two digits of numbers. Our goal is to convert all single-digit numbers to a double-digit number but there is a zero number in front. The final score is in the 'T' column. See more columns in the picture. I deliberately set up an image with these help columns because I will try to explain step by step how I create the final formula in which nested multiple formulas. At the end of this tutorial, you will see two long formulas. They were created in a similar way but I used different Excel functions, but the result is the same.

  1. Step 1. isolation of a group of numbers before the first slash (red font)
  2. Step 2. extract numbers between two slashes (blue font)
  3. Step 3. extract numbers after the last slash (green font)

Adding zero in front of a single-digit number

Step 1

How to extract all the numbers in front of the first slash

In the first step I set a group of results in 'C', 'D' and 'E' columns. In these columns I went to solve the first group of digits, which is located in front of the first slash (red font in the picture in the 'A' column). To be extracted all digits before the slash I used the following formula:

Formula in 'C1' cell (result is number formatted as text): (I will explain this formula in more detail and leave you to study other formulas, of course using the F9 key and the Formula Evaluate. Note that this formula extracted all digits before the first slash regardless of whether one or two digits or the number). Using Excel 'FIND' function I was looking for the position of the first slash a result of these functions is the number 2 ie the position of the first slash. Using 'IFERROR' function I returned a result the number of digits including the slash but I reduce the '-1' because of slashes. Finally, using the 'LEFT' function, extracted the result from the 'A1' cell. Of course you copy the formula down.
=LEFT(A1;IFERROR(FIND("/";A1);99)-1)

The formula in the 'D1' cell, counts how many digits it has in the 'C1' cell. (Result is formatted as a number value): When you copy the formula down then we can see for each line how many digits there for all the numbers that are located in front of the first slash.
=LEN(C1)

Formula in 'E1' cell (red font and result is a number with two digits formatted as text): (Using the 'IF' function I set the following condition: If the number of digits in 'D1' is smaller than '2' then add the digit Zero (0) in front of the number in the 'C1' cell.
This term "0"&C1 can be replaced by CONCATENATE("0";C1). In case the logic condition is fulfilled, the IF function returns the number of digits greater than 2.
=IF(D1<2;"0"&C1;C1)

This IF formula is important for this tutorial and my clarification. For beginners, I want to explain how to embody the formula within another formula. Once again look at the IF formula above. Note that there are two cells in it, such as 'C1' and 'D1'. If we want to avoid auxiliary columns 'C' and 'D', then we can nest formula respectively from the columns in this IF formula. This means that instead of each address of the ether 'C1' and 'D1' we can put another formula.
=IF(D1<2;"0"&C1;C1)

It looks like this formula in the 'Q1' cell (red font and the result is a two-digit number formatted as text). So I solved the formula for the first group of digits before the slash.
=IF(LEN(LEFT(A1;IFERROR(FIND("/";A1);99)-1))<2;"0"&LEFT(A1;IFERROR(FIND("/";A1);99)-1);LEFT(A1;IFERROR(FIND("/";A1);99)-1))

Step 2

How to sort out all the numbers between the two slashes

The next group of formulas is in the columns 'G', 'H' and 'I'. Again similar formula as in the first set but the difference is to extract numbers between two slashes. The other two formulas are the same.

The formula in the 'G1' cell: (This formula stands out all the numbers between the two slashes. See the results for the other rows in this formula.)
=TRIM(MID(SUBSTITUTE(A1;"/";REPT(" ";255));255;255))

The formula in 'H1' cell counts how many digits it has in the '' H1 cell. (the result is a number formatted as a value):
=LEN(G1)

Formula in 'I1' cell (blue font and result is a two-digit number formatted as text):
=IF(H1<2;"0"&G1;G1)

Of course again, we want to avoid the auxiliary columns, so in the 'R1' cell the following formula is created (by the same principle of nested functions as in the previous case).
=IF(LEN(TRIM(MID(SUBSTITUTE(A1;"/";REPT(" ";255));255;255)))<2;"0"&TRIM(MID(SUBSTITUTE(A1;"/";REPT(" ";255));255;255));TRIM(MID(SUBSTITUTE(A1;"/";REPT(" ";255));255;255)))

How to sort out all the numbers behind the last slashes

Step 3

The next group of formulas is in columns 'K', 'L' and 'M'. Of course, I also used a similar formula as in the first and second group but the difference is to extract numbers after the last slash. The other two formulas are the same.

The formula in the 'K1' cell: (This formula extract all the numbers after the last slash. See the results for the other rows in this formula.)
=RIGHT(A1;LEN(A1)-FIND("*";SUBSTITUTE(A1;"/";"*";LEN(A1)-LEN(SUBSTITUTE(A1;"/";"")))))

The formula in 'L1' cell counts how many digits it has in '' K1 cell. (the result is a number formatted as a value):
=LEN(K1)

Formula in 'M1' cell (green font and result is a two-digit number formatted as text):
=IF(L1<2;"0"&K1;K1)

Of course again, we want to avoid auxiliary columns, so in the 'S1' cell (green font) the following formula is created (by the same principle as nested functions as in the previous case).
=IF(LEN(RIGHT(A1;LEN(A1)-FIND("*";SUBSTITUTE(A1;"/";"*";LEN(A1)-LEN(SUBSTITUTE(A1;"/";""))))))<2;"0"&RIGHT(A1;LEN(A1)-FIND("*";SUBSTITUTE(A1;"/";"*";LEN(A1)-LEN(SUBSTITUTE(A1;"/";"")))));RIGHT(A1;LEN(A1)-FIND("*";SUBSTITUTE(A1;"/";"*";LEN(A1)-LEN(SUBSTITUTE(A1;"/";""))))))

In this way we have solved individually positioning each group of numbers before, between and after the slashes. The next task is to combine all three formulas into one formula to have the final desired result.

How to extract numbers between a specific character and add a zero in front of a one-digit number

In the 'O1' cell, you can see a simple formula. So, this formula replaces the CONCATENATE formula and looks like below.
=Q1&"/"&R1&"/"&S1

Of course, to make it all again to one formula, let's go into this formula above, all the finite formulas we've created in three steps at the beginning of the tutorial. So, instead of the cell address we can put a formula in the relevant cells.

The formula in the 'T1' cell (pink font) is the following: (Note that in the formula below there is only the 'A1' address of the cell, this formula contains 575 characters).
=IF(LEN(LEFT(A1;IFERROR(FIND("/";A1);99)-1))<2;"0"&LEFT(A1;IFERROR(FIND("/";A1);99)-1);LEFT(A1;IFERROR(FIND("/";A1);99)-1))&"/"&IF(LEN(TRIM(MID(SUBSTITUTE(A1;"/";REPT(" ";255));255;255)))<2;"0"&TRIM(MID(SUBSTITUTE(A1;"/";REPT(" ";255));255;255));TRIM(MID(SUBSTITUTE(A1;"/";REPT(" ";255));255;255)))&"/"&IF(LEN(RIGHT(A1;LEN(A1)-FIND("*";SUBSTITUTE(A1;"/";"*";LEN(A1)-LEN(SUBSTITUTE(A1;"/";""))))))<2;"0"&RIGHT(A1;LEN(A1)-FIND("*";SUBSTITUTE(A1;"/";"*";LEN(A1)-LEN(SUBSTITUTE(A1;"/";"")))));RIGHT(A1;LEN(A1)-FIND("*";SUBSTITUTE(A1;"/";"*";LEN(A1)-LEN(SUBSTITUTE(A1;"/";""))))))

This formula below returns the same result as the formula above. So, in the 'T1' cell (pink font) can be the following formula: (Note that in the formula below there is only the 'A1' address of the cell. This formula contains 869 characters).
=IF(LEN(IF(MID(A1;2;1)="/";LEFT(A1;1);LEFT(A1;2)))<2;"0"&IF(MID(A1;2;1)="/";LEFT(A1;1);LEFT(A1;2));IF(MID(A1;2;1)="/";LEFT(A1;1);LEFT(A1;2)))&"/"&IF(LEN(IF(MID(MID(A1;SEARCH("/";A1;1)+1;4);2;1)="/";LEFT(MID(A1;SEARCH("/";A1;1)+1;4);1);LEFT(MID(A1;SEARCH("/";A1;1)+1;4);2)))<2;"0"&IF(MID(MID(A1;SEARCH("/";A1;1)+1;4);2;1)="/";LEFT(MID(A1;SEARCH("/";A1;1)+1;4);1);LEFT(MID(A1;SEARCH("/";A1;1)+1;4);2));IF(MID(MID(A1;SEARCH("/";A1;1)+1;4);2;1)="/";LEFT(MID(A1;SEARCH("/";A1;1)+1;4);1);LEFT(MID(A1;SEARCH("/";A1;1)+1;4);2)))&"/"&IF(LEN(TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE($A1;"/";REPT(" ";LEN($A1)));LEN($A1)*2;LEN($A1));"/";REPT(" ";255));255)))<2;"0"&TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE($A1;"/";REPT(" ";LEN($A1)));LEN($A1)*2;LEN($A1));"/";REPT(" ";255));255));TRIM(LEFT(SUBSTITUTE(MID (SUBSTITUTE($A1;"/";REPT(" ";LEN($A1)));LEN($A1)*2;LEN($A1));"/";REPT(" ";255));255)))

I hope that I am somewhat managed to explain to beginners how to create a very long formula and to nest within the formula of another formula and how to add a zero digit in front of the number.

Finally, in the attachment of this tutorial, you have the Download file option on which I did this tutorial.

Pay attention, you may need as a delimiter of arguments formula use a comma (,) instead of a semicolon (;).
Copy formula to Notepad and with Find/Replace change semicolon to comma.