# extracting numbers from alphanumeric strings

1. ## extracting numbers from alphanumeric strings

First time in this forum. Hi all. I have a challenging question, is there a way either by VBA or manually (preferably both, if possible) to actually unite the X amount of numbers that are in a cell given the contents is alphanumeric? I'll give you the following examples to see if you can understand what I' referring to?

DATA output should be
asd67,h876 --------> 67876
2,3,ujdj5&34 -------> 23534
909k86m34 --------> 9098634

Hope this makes sense?

2. for up to 16 characters this will work
wait for it
its looooong
surely there must be a beter way(with functions) ? in fact i know it can be done by vba, but i just like messing with functions

3. ## Challenging: extracting numbers from alphanumeric strings

Try this

A1: abc123def456ghi789

First, create a Named Formula
Names in Workbook: Seq
Refers to: =ROW(INDEX(\$1:\$65536,1,1):INDEX(\$1:\$65536,255,1))

This ARRAY FORMULA removes ALL non-numerics from a string
B1: =SUM(IF(ISNUMBER(1/(MID(A1,seq,1)+1)),MID(A1,seq,1)*10^MMULT(-(seq<TRANSPOSE(seq)),-ISNUMBER(1/(MID(A1,seq,1)+1)))))

In the example, the formula returns: 123456789

4. Ron, that is very clever.

It appears that seq can be defined as =ROW(\$1:\$255). Is there a disadvantage to doing so?

5. Thanks for the replies. Martindwilson you wasn't kidding about how loooong that formulae was, but it worked. Thanks. Ron, not quite sure on how to create Named formulae. A bit confused on that part, as for the rest, I'm quite o.k. Can you provide me with some guidance as to how this is done. The formula seems very interesting and I would like to see it applied. Much appreciated.

6. Insert > Name > Define, seq Refers to: =ROW(INDEX(\$1:\$65536,1,1):INDEX(\$1:\$65536,255,1))

7. OK! Got it! Thanks Shg. I tried it and it worked like a charm. Thanks again to all for the great help and very interesting manner of obtaining the desired result. Truly surpassed my expectations.

8. ## Alternative range ref

Originally Posted by shg
Ron, that is very clever.

It appears that seq can be defined as =ROW(\$1:\$255). Is there a disadvantage to doing so?
Thanks, shg...The only potential hazard to using ROW(\$1:\$255) is the
insertion of rows at Row_1...which would shift the reference to ROW(\$2:\$256)...etc

I also played with ROW(\$1:\$65536)...the end result....
Excel unceremiously exits without so much as a warning. (no idea why)

I'd stick with the formula as posted.

9. Good point.

My first thought was ROW(INDIRECT("1:255")), which is invariant, but the INDIRECT is volatile.

10. Hi guys,

Nice formula Ron. Here is another formula that it will work. But it uses INDIRECT and we all know it is volatile. It seems to calculate faster maybe because it uses less functions.

The formula is an-array need to hold down;

Ctrl,Shift,Enter

Hope it helps!

11. ## Challenging: extracting numbers from alphanumeric strings

How about spitting the difference between Harlan Grove's formula (the one I posted)
and the formula you posted....

This variation uses a method to avoid CTRL+SHIFT+ENTER while
still using the Seq named range....
I like that blend. It avoids the array enter AND the MMULT function
(which is not really intuitive at all).

12. Originally Posted by Ron Coderre
How about spitting the difference between Harlan Grove's formula (the one I posted)
and the formula you posted....

This variation uses a method to avoid CTRL+SHIFT+ENTER while
still using the Seq named range....
I like that blend. It avoids the array enter AND the MMULT function
(which is not really intuitive at all).

Now that is a formula I'll be using for now on. Great job Ron!! But too bad there's not a all in one formula to extract letters just using the functions that are native to excel. Thats a real challenge.

13. Nice formula, Ron.
//Ola

14. Originally Posted by Ron Coderre
Thanks, shg...The only potential hazard to using ROW(\$1:\$255) is the
insertion of rows at Row_1...which would shift the reference to ROW(\$2:\$256)...etc

I also played with ROW(\$1:\$65536)...the end result....
Excel unceremiously exits without so much as a warning. (no idea why)

I'd stick with the formula as posted.
Ron, that's a [yet another!] tremendous formula.

With the original Harlan Grove formula, I believe Excel crashes because of limitations with the transpose function: there is a 255 character per array element and there is a limit on the number of elements allowed (I believe this depends on the version of Excel and available memory) when using transpose.

Out of curiosity and trying to understand the mechanics, I had a play around and I managed to remove one of the nested Index functions:
=SUM(INDEX(MID(0&A1,INDEX(LARGE(INDEX(ISNUMBER(--MID(A1,Seq,1))*Seq,0),Seq),0)+1,1)*10^Seq/10,0))

Changed to:
=SUM(INDEX(MID(0&A1,LARGE(INDEX(ISNUMBER(-MID(A1,seq,1))*seq,0),seq)+1,1)*10^seq/10,0))
Although, I've probably walked into some unforseen problems with this?

Another variation is to use Sumproduct to remove another one of the index functions:
=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(-MID(A1,seq,1))*seq,0),seq)+1,1)*10^seq/10)
So I guess those are other options, but I have no idea which is best?

Thank you for sharing this 'Index' technique to workaround having to CTRL+SHIFT+ENTER the formula - it's really fascinating.

Colin

15. ## extracting numbers from alphanumeric strings

Yeah....I guess I got a little overzealous with the INDEX function.
I would:
1) Use a slightly edited version (see 2 below) of the last variation you posted:
but
2) Use --MID instead of -MID
because --MID clearly indicates that we're coercing text values to numeric.
Using -MID, while shorter by one character, is less obvious. In
very complex formulas that need de-bugging, it looks like a piece of
the formula is missing or a typo.

Page 1 of 2 1 2 Last

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1