See post 6 here:
http://www.mrexcel.com/forum/showthread.php?t=362184
or post 3 here:
http://www.mrexcel.com/forum/showthread.php?t=440487
Hope that helps.
Dear Smartest Excelers Around,
Is there a one cell formula that could take this string in cell A1:
45t*&65/
and extract only the numbers and deliver this
4565
to a single cell?
The formula would have to be able to deal with all 255 ASCII characters and be copied down a column.
Sincerely, Mike Girvin
See post 6 here:
http://www.mrexcel.com/forum/showthread.php?t=362184
or post 3 here:
http://www.mrexcel.com/forum/showthread.php?t=440487
Hope that helps.
Always make a back up copy before trying new code, you never know what you might lose!
- Posting guidelines, forum rules and terms of use
-Try searching for your answer first, see how
- Read the FAQs
- List of BB codes
"The world suffers a lot. Not because of the violence of bad people, but because of the silence of good people!"
Try this:
With
A1 containing an alphanumeric string: eg 9128ABC37DEF465
This ARRAY FORMULA, committed with CTRL+SHIFT+ENTER
(instead of just ENTER) returns ONLY the numbers:
In the above example, the formula returns: 912837465Code:B1: =SUM(MID(A1,LARGE(ISNUMBER(--MID(A1,ROW($1:$25),1))* ROW($1:$25),ROW($A$1:INDEX($A:$A,COUNT(--MID(A1,ROW($1:$25),1))))),1)* 10^(ROW(INDEX($1:$25,COUNT(--MID(A1,ROW(INDEX($1:$25,1,1): INDEX($1:$25,LEN(A1),1)),1)),1):INDEX($1:$25,1,1))-1))
That formula works for text up to 25 characters long.
Is that something you can work with?
Last edited by Ron Coderre; Jan 26th, 2010 at 04:10 PM. Reason: Typo...Chgd sumproduct to sum
Best Regards,
Ron Coderre
Microsoft MVP-Excel (2006 - 2015)
Using: Excel 2013 & 2016
I played around with the formula some more...
This non-array formula seems to be working:
Code:B1: =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)
Best Regards,
Ron Coderre
Microsoft MVP-Excel (2006 - 2015)
Using: Excel 2013 & 2016
Ron, is there a way to modify the formula to get the 2nd number to the right instead of the sum? The result is 37.
Got it! I understand...evidently, the last coffee molecule just bumped into my brain.
This formula returns the second sequence of numbers from a string in the format of: digits letters digits
I'm hoping that can be shortened.Code:B1: =LOOKUP(99^99,--("0"&MID(MID(A1,LEN(LOOKUP(10^99,--LEFT(A1,ROW($1:$25) )))+1,25),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},MID(A1,LEN(LOOKUP(10^99,--LEFT(A1, ROW($1:$25))))+1,25)&"0123456789")),ROW($1:$25))))
That formula returns these results:
For: 45t*&65/
result: 65
For: 9128A+BC37/E*465
result: 37
Does that help?
Best Regards,
Ron Coderre
Microsoft MVP-Excel (2006 - 2015)
Using: Excel 2013 & 2016
Schielrn,
The formula from Domenic at post 6:
http://www.mrexcel.com/forum/showthread.php?t=362184
works great (I changed my string to cell A2):
=SUM(MID(0&A2,LARGE(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))*ROW(INDIRECT("1:"&LEN(A2))),ROW(INDIRECT("1:"&LEN(A2))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A2)))/10)
Ron Coderre,
This formula is beautiful (I changed my string to cell A2):
=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW($1:$25),1))*
ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)
Ron, I like your formula because of its compactness and non-array non-Ctrl-Shift-Enter.
I like Domenic’s because of the robustness of the ROW(INDIRECT("1:"&LEN(A2))).
Mashing your two formulas together (in Excel 2007 only – exceeds 7 nesting rule in Excel 2003) I got:
=SUMPRODUCT(MID(0&A2,LARGE(INDEX(ISNUMBER(--MID(A2,ROW(INDIRECT("1:"&LEN($A$2))),1))*
ROW(INDIRECT("1:"&LEN($A$2))),0),ROW(INDIRECT("1:"&LEN($A$2))))+1,1)*10^ROW(INDIRECT("1:"&LEN($A$2)))/10)
I learn a lot from both formulas. In essence, are these the concepts of how the formula is working:
1) Extract characters with MID
2) Convert extracted characters to numbers and errors with --
3) Ask array if elements are numbers with ISNUMBER
4) Multiply array of TRUE/FALSE by position -- *ROW(INDIRECT("1:"&LEN($A$2)))
5) Organize by position with LARGE (largest to smallest including zeroes)
6) Add 1 to avoid error with MID caused by zeros from LARGE
7) Concatenate a zero to beginning of string so the 1 added to the zeros will not cause error when MID extracts numbers
8) Multiply the extracted numbers by 10^ROW(INDIRECT("1:"&LEN($A$2))) to get the correct number of zeros for each extracted number
9) Divide by 10 to deal with the fact the we had a zero concatenated to the front of the string
10) Added
Am I getting the concepts, right?
Sincerely, Mike Girvin
It looks I should definitely consider upgrading to Excel 2007. Probably, exceeding 7 nested brackets. How would I make this work with Excel 2003? Can I separate it into two columns?
As much as I loathe giving array formula solutions
(nobody I work with ever remembers to CSE them),
I think there's a certain elegance to this one:
Does that help?Code:B1: =LOOKUP(10^99,--MID(A1,MIN(IF((--ISNUMBER(--MID(A1,ROW($1:$25),1))=0)* ISNUMBER(--MID(A1,ROW($2:$26),1)),ROW($2:$26))),ROW($1:$25)))
Best Regards,
Ron Coderre
Microsoft MVP-Excel (2006 - 2015)
Using: Excel 2013 & 2016
Like this thread? Share it with others