Extract number from a text string
This post is about two things:
- How a poor system set up could drive you crazy?
- Why being good in Excel could save you lots of time in workplace?
Another real work example. I was given a simple task: “Pls get the data of money spent from the web-based report portal and see how many entries were below budget.” Isn’t it a simple task? I think so… until I got the data. The screenshot below shows part of the data:
Here comes the 1st question – How a poor system set up could drive you crazy?
The system set up for this data field is totally unprofessional. Unfortunately, this is not uncommon in real workplace.
The data was all input as text string, with different currencies input manually before or after the number. Worse still, there is no consistency in the data and there were more than 5000 rows of such data. What I needed was to extract the number portion from the text string. Would this “simple task” now drive you crazy?
Fortunately enough, after just a moment of frustration, I managed to solve the above problem with an array formula:
=MID(B2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")),COUNT(0+MID(B2,ROW(INDIRECT("1:"&LEN(B2))),1)))+0
CTRL SHIFT ENTER
And this explains the second question: Why being good in Excel could save you lots of time in workplace?
Imagine how much time required to solve the problem if to be done manually (remember the fact that there were more than 5000 rows in my case). Needless to say the accuracy from manual work. I am not going to explain how the above formula works in details now. However, I would like to draw your attention to the functions used. They are all simple basic functions in Excel.
- MID
- MIN
- FIND
- COUNT
- ROW
- INDIRECT
- LEN
- &
- +
Maybe you know most, if not all, of them already. However, have you ever put them altogether and do something helpful in your work? Honestly, when I first encountered these functions, I could hardly imagine how they could be applied practically. Now I do. 🙂
I hope you will get some insights about what Excel could do for you other than table drawing or + – * / after reading this post. Btw, if you wish to understand how the above formula works, leave a comment.
wmfexcel 
Formula and vba coding
LikeLike
While the above formula is impressive, it fails on the following text strings:
7-ft Tree PKU 31987645
World Globe Price 122.56
This array formula can only be used if the text string contains just one number. Furthermore, it can’t handle decimals.
LikeLike
Hi Michael,
Thanks for your comment.
You are right. The formula does not serve the situations you mentioned. Instead of discussing a super formula that can handle “most” situations, I tried to demonstrate the power of formulation in Excel. Somethings seem not possible are actually doable.
Cheers,
LikeLike
Got an enquiry about MID function.
MID is used to extract part of a text string. you may specific the starting position and the number of characters you need.
They syntax is MID(text,start_num,num_chars)
For example:
=MID(“abcdedfg”,3,2) returns “cd” as it looks at the 3rd position from the text string, and get 2 characters starting from there.
LikeLike