Home
Tips & Tricks
Excel Functions
Excel Formulas
Applied Formulas
Applied Functions
Excel Macros
Business Uses
Downloads
Excel Tests
Latest Updates
Recent Posts
About us
Q&A
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:
=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.
Share this page:
Enjoy this page? Please pay it forward. Here’s how…
Would you prefer to share this page with others by linking to it?
- Click on the HTML link code below.
- Copy and paste it, adding a note of your own, into your blog, a Web page, forums, a blog comment, your Facebook account, or anywhere that someone would find this page valuable.
<a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a><a href=”http://www.advanced-excel.com/”>Advanced Excel – From a Business Perspective</a>
Excel Courses for Business Professionals
Copyright © advanced-excel.com 2007 – 2019. All Rights Reserved. Privacy Policy
Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
advanced-excel.com is in no way associated with Microsoft
