[1]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 [2]Wolf would be able to do this in his sleep.
Also on:
[3]Twitter
__________________________________________________________________
My original entry is here: [4]How to Extract a Number or Text from
Excel with this Function. It posted Mon, 07 Jan 2019 01:15:44 +0000.
Filed under: tech,
References
1.
https://www.makeuseof.com/tag/extract-number-text-excel-function/
2.
https://jwgoerlich.com/
3.
https://twitter.com/prjorgensen/status/1082084314693685249
4.
https://www.prjorgensen.com/?p=2488
