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?
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
Please Login or Register to view this content.
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
Ron, that is very clever.
It appears that seq can be defined as =ROW($1:$255). Is there a disadvantage to doing so?
Last edited by shg; 08-16-2008 at 12:46 PM.
Thanks for the replies.
Insert > Name > Define, seq Refers to: =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,255,1))
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.
Thanks, shg...The only potential hazard to using ROW($1:$255) is theOriginally Posted by shg
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.
Good point.
My first thought was ROW(INDIRECT("1:255")), which is invariant, but the INDIRECT is volatile.
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;Please Login or Register to view this content.
Ctrl,Shift,Enter
Hope it helps!
Last edited by vane0326; 08-17-2008 at 12:44 AM.
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 functionPlease Login or Register to view this content.
(which is not really intuitive at all).
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 functionPlease Login or Register to view this content.
(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.
Nice formula, Ron.
//Ola
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
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:
butPlease Login or Register to view this content.
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.
How about a string like " 14TB56ATLFINAL". I want to separate numeric numbers and characters into different cells. For example, "14TB56ATLFINAL" would be:
A1=14TB56ATLFINAL
B1= 14
C1= TB
D1= 56
E1= ATL
=NPV(-0.9,,IFERROR((MID(A1,256-COLUMN(A:IV),1)%),""))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
asd67,h876 2,3,ujdj5&34 909k86m34
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Sorry it has taken so long to answer. The formula works perfect except that I wanted to separate the occurrences of numeric values in different cells. For example:
Say
A1 = 10MIN42GBFINAL
then
B1 will have 10
C1 will have MIN
D1 will have 42
E1 will have GB
F1 will have FINAL (can be discarded.)
So the logic I am thinking of is first checking if a string is numeric or char and stopping when it changes. This means (in my amateur mind, that the formulas are going to be dependent on each other (if-then Boolean scenario)
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Sorry about that. As you can see, it was my very first question. My bad.
Not a problem, it happens
Just an attempt without seq, but with indirect, an ARRAY formula
Please Login or Register to view this content.
Hi Ron,
This function doesn't seems to be working on Excel 2013... I tried but not getting result...
Also please tel me why using seq as it always returns 1 even the transpose one as well...
Please have a look and let me know...
Thanks,
AISM
Gosh!!
I forgot to press CTL + ALT + Enter it's working now, but going above my understanding... Kindly elaborate what is happening and how to call it through VBA?
aism,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
Ben Van Johnson
If you have Excel 2013 or later versions, the Flash Fill feature can help you to separate text and numbers quickly and easily.
Last edited by aasunshine; 08-22-2016 at 11:07 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks