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.

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: