How to Extract a Number or Text from Excel with this Function:
With the strategies above, you should be able to extract numbers or text out of most mixed-format cells that are giving you trouble. Even if they don't, you can probably combine them with some of the powerful text functions included in Microsoft Excel to get the characters you're looking for. However, there are some much more complicated situations that call for more complicated solutions.
For example, I found a forum post where someone wanted to extract the numbers from a string like "45t*&65/", so that he would end up with "4565." Another poster gave the following formula as one way to do it:
=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)To be completely honest, I have no idea how it works. But according to the forum post, it will take the numbers out of a complicated string of numbers and other characters. The point is that, with enough time, patience, and effort, you can extract numbers and text from just about anything! You just have to find the right resources.
This formula is huge for me time-wise. I am working on better financial management here in Japan and my bank likes to add text, commas, and ¥ to the yen amounts. This strips them for me.
I'm sure my buddy Wolf would be able to do this in his sleep.
Also on: