• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract all the numeric values from the right of any alphanumeric string

Thank you Haseeb A,


I was expecting such a brilliant response from you....Kudos...


By work you are ....but I am sure that that very soon you will receive your ninja tag hear at chandoo.org as well.


Regards,

Kuldeep
 
@ Haseeb A,


Your formula work great and its faster but failing at for dataset like


CT-012----CT-035

CT-001----CT-011

CT-036----CT-045


Can we overcome this as well.


@ Luke M,


Your UDF is stuck at data set like


5020000059------5020000093

5010000485------5010000518

5010000483------5010000484


Can we overcome this as well.


P.S>>> Both the formulas are working. LukeM UDF giving right result where Hasseb A formula stuck and vice versa.


Regards,
 
Hi Kuldeep,


What would have been the desired output for CT-012----CT-035 or 5020000059------5020000093??
 
For CT


CT-012----CT-035 >>>>> 12 >>>>> 35


For number


5020000059------5020000093 >>>>> 5020000059 >>>> 5020000093


Faseeh, I wish if you can go through from the first post to answer it as we have moved a lot from origin.


Regards,
 
Hi Kuldeep,


For First Case "CT-012----CT-035" present in Cell A1:


For 012 Use:

=TEXT(TRIM(MID(LEFT(A1,FIND(".",SUBSTITUTE(A1,"-",".",2),1)-1),FIND("-",LEFT(A1,FIND(".",SUBSTITUTE(A1,"-",".",2),1)-1),1)+1,10)),"#")


For 035 Use:

=TEXT(TRIM(MID(SUBSTITUTE(A1,"-",".",SUM((MID(A1,ROW(A1:A25),1)="-")*1)),FIND(".",SUBSTITUTE(A1,"-",".",SUM((MID(A1,ROW(A1:A25),1)="-")*1)),1)+1,20)),"#") [Ctrl+Shift+Enter]


For Second Case "5020000059------5020000093" in Cell A2:


For 5020000059 Use:

=MID(SUBSTITUTE(A2,"-",".",1),1,FIND(".",SUBSTITUTE(A2,"-",".",1),1)-1)


For 5020000093 Use:

=TEXT(TRIM(MID(SUBSTITUTE(A2,"-",".",SUM((MID(A1,ROW(A1:A25),1)="-")*1)),FIND(".",SUBSTITUTE(A2,"-",".",SUM((MID(A1,ROW(A1:A25),1)="-")*1)),10)+1,15)),"#") [Ctrl+Shift+Enter]


Hope that works!!


Faseeh
 
Sorry Faseeh,


It seems to be me that we are going in wrong direction. Actually I have used the above UDF and formula and wish to get in continuation to Luke M or Hasseb A formula.


Actually it all about Extracting all the numeric values from the right of any alphanumeric string


Regards,
 
Hi Kuldeep,


I thought that CT-012----CT-035 is present in a single cell and you want 012 and 015 in two separate cells. Similarly i assumed 5020000059------5020000093 in a cell and you wanted them to be in two diff. cells with dashes removed.


Now I assume just want to fetch 012? Is that correct? and what about the No. 50200059 I think it does not have any alphabet attached to it!


Regards,
 
Yes Faseeh,


Actually there are almost 2 LAC ranges of such numbers. They can have any alphanumeric format like


Only Numbers >> Example : 95665656

Starting with Alphabet but finishing at numbers >> Example ABC956565

Starting with Alphabet and Finishing at alphabet having number in between >> Example : ABC12A365P


Now first we need to identify the rightmost numbers available in string. for above examples


95665656 will be 95665656 : All numbers

ABC956565 will be 956565 : Rightmost numbers

ABC12A365P will be 365 : Rightmost numbers


CT-012 will be : 12


Hope that make sense...


Regards,
 
Kuldeep,

The UDF I last posted seems to produce the results you require as defined in your last post. Is it not working on your end, or are you wishing something else?
 
Luke M,


Your UDF worked perfect for me till today and my requirement is also same. I have used it for over 150K data today and it gave me 52 error for two types of data set. Today my data had few 10 digit number and it gave me error.


5020000059------5020000093

5010000485------5010000518

5010000483------5010000484


Another one was having decimal (.) in between hence there was some issue. still in thinking how to handle this one. (at present skip this)


Please refer http://chandoo.org/forums/topic/extract-all-the-numeric-values-from-the-right-of-any-alphanumeric-string#post-32759


Regards,
 
Ah, I see. Problem is that I defined the function as a Long, which has a limit of 2,147,483,647. To allow for bigger numbers, change it to Double, like so:

[pre]
Code: 
Function LastNumber(s As String) As Double
With the decimal issue, are you guaranteed that there will never be more than 1 decimal in the cell? If so, we can change this line

[pre][code]If xNumber Like "[0-9]" Then
[/pre]
to this

If xNumber Like "[0-9]" Or xNumber = "." Then[/code][/pre]
 
Kuldeep,


If the issue is getting 'minus' values, add ABS with lookup.


Code: 
=ABS(LOOKUP(9E+300,RIGHT(LEFT(0&A1,MATCH(10,INDEX(MID(0&A1,ROW(A$1:INDEX(IV:IV,LEN(A1)+1)),1)+0,0))),ROW(A$1:INDEX(IV:IV,LEN(A1)+1)))+0))


Also, INDIRECT is a volatile function, if you have huge data set this will slow down the calculation, so replaced with INDEX & use non usable column like IV. SO that way only recalculate when open the workbook, something changes in A1 or the number ie A1, A2, A3 cell, insert or delete rows.


Hope this helps.
 
Hi Luke,


I am using your UDF as "=IF(B3="No data",((LastNumber(C3)-LastNumber(B3))+0),((LastNumber(C3)-LastNumber(B3))+1))" form B2 to B3000


It works fine...Point I would like to know is that it always gives me #Name error when workbook is open and F9 is pressed to calculate sheet however if I press F2 in B2 and Enter, it gives me the correct answer and now pressing F9 will calculate all the 3000 rows.


It is something like i need to wakeup excel to take care of this UDF. This was never seen before function was used as "Long" and appeared after changing this to "Double"


I have even tried CTRL+ALT+F9 for full calculation.


Regards,
 
Hmm. Not sure why changing the type should have mattered. You could try changing the beginning of function to this:

[pre]
Code: 
Function LastNumber(s As String) As Double
Application.Volatile
'...etc
End Function
[/pre]
The Volatile method lets XL know to recalculate every time. I don't think this "should" be needed, but some strange stuff might be going on due to it being inside an IF function or something.
 
Agreed. Different idea...let's reword your function a little:

=LastNumber(C3)-LastNumber(B3)+(B3<>"No Data")


This is the equivalent of what you had before and should work a little faster.
 
Hi, all!

Excel (in all its versions) has a very poor refreshing of UDF value. I don't know in this case as I didn't read all the posts, but each time I read something about UDF, Volatile or Ctrl-Alt-F9 I painly remember this:

http://chandoo.org/forums/topic/udf-lose-their-value-until-ctr-alt-f9

Regards!

PS: Still pending...
 
Hi guys can you help as i have to split the cell BR-1200-1400 as under :


BR-1200-1400 into BR in one cell then 1200 in next cell and 1400 in the third cell.

thanks

nojpatel
 
Hi Patel ,


The easiest way is to use the Text to Columns feature , using the hyphen ( - ) as a delimiter.


Do you want a formula-based solution ? In such a case , will the hyphen always separate the fields you wish put in the different cells ?


Narayan
 
Luke, Haseeb and Narayan

Great work here.

Others, thank you for wonderful questions. I have been able to use some of the answers from here. However I had a slightly different request from Kuldeep.

In my case I want ALL the numbers between text.

However there are only two letters MAX and two sets of numbers. They need to be extracted into two separate columns.


Here is my situation, data and what I want to extract. Given that my sheet may have about 2,000 rows.


Cell A1: W144.8 N82.11 Extract==> Cell B1: 144.8 Cell C1: 82.11

Cell A2: W23 N278.5 Extract==> Cell B2: 23 Cell C2: 278.5

Cell A3: W123.4 N223 Extract==> Cell B3: 123.4 Cell C3: 223


After going through the posts. I was impressed by several answers.

Following answer, I know I can use in column C, to exract outer number values from Column A

=LOOKUP(9E+300,RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1))))+0)


I need formula to put in column B to extract inner number values from Column A.


Any help would be greatly appreciated.


Kind regards

Kulmansam
 
@all

kulmansam has created a new thread for his question (thanks!)

Thread link is:

http://chandoo.org/forums/topic/need-help-extracting-numbers-from-a-string-that-has-mix-of-numbers-and-letters#post-103133
 
You must log in or register to reply here.
Back
Top