Extract numbers from a cell

The currency volume extracted using the index and match formula in the previous issue are all squeezed into one single cell. Extracting them involves some more formulas which worked on the cell level. One of them is the TRIM formula which allows us to remove extract spaces at the beginning and end of the text. Not only that, it also removes extract spaces in between each word or number. This helps to separate the numbers in each cell with only a single space.

Extract Text from a cell

The formula is simply =TRIM(B2).

With the numbers separated by a single space, it is easier for us to identify them. Using the SUBSTITUTE formula, we can insert a large number of spaces between the numbers. 100 is used because it is easy to count and remember.  The formula becomes =SUBSTITUTE(TRIM(B2)," ",REPT(" ", 100)). Instead of typing 100 spaces, the effort can be shorten using the REPT formula (known as the Repeat formula). With 100 spaces between the numbers, the chances of the number falling between the first 100 spaces and the next 100 spaces is almost 100% if not 100%. Using the MID, we can extract the number between the first 100 spaces and the 2nd 100 spaces.

=MID(SUBSTITUTE(TRIM($B2)," ",REPT(" ", 100)),100,100)

The formula extracts the second number which is between the first and second space. If we want the formula to extract from the first number , we have to add in a space before the TRIM formula so that the first 100 spaces appears before the first number instead of the second number as shown.

=MID(SUBSTITUTE(" "&TRIM($B2)," ",REPT(" ", 100)),100,100)

With the number extracted with trailing spaces before and after the number, we have to apply the TRIM formula again to get rid of the spaces.

=TRIM(MID(SUBSTITUTE(" "&TRIM($B2)," ",REPT(" ", 100)),100,100))

For the second number, we have to change the number is bold to 200, third number to 300, etc.

=TRIM(MID(SUBSTITUTE(" "&TRIM($B2)," ",REPT(" ", 100)),200,100))

We can automate the change of the number by using the column formula. The column() formula will return 2 for column B. To start the first 100 from B1, we have to subtract 1 from the COLUMN formula and multiply it to the orignal 100. The final formula in B2 should look like this:

=TRIM(MID(SUBSTITUTE(" "&TRIM($B2)," ",REPT(" ", 100)),(COLUMN()-1)*100,100))

Hope you like the solution. This solution is given by an unnamed Excel MVP.




New! Comments

Have your say about what you just read! Leave me a comment in the box below.