• 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.

HOW TO SEPARATE TEXT AND NUMBERS ?

Hi Suri,


Considering that the value BLR5658DTDC is in cell A1 and 3866DTDCBLR is in cell A2


Please use the below formula to extract numbers from the examples you have given.


=IFERROR(MID(A1,(MIN(IFERROR(SEARCH({1,2,3,4,5,6,7,8,9,0},A1,1),""))),(MIN(IFERROR(FIND(CHAR(ROW($A$65:$A$122)),A1,MIN(IFERROR(SEARCH({1,2,3,4,5,6,7,8,9,0},A1,1),""))),"")))-(MIN(IFERROR(SEARCH({1,2,3,4,5,6,7,8,9,0},A1,1),""))))*1,RIGHT(A1,LEN(A1)-(MIN(IFERROR(SEARCH({1,2,3,4,5,6,7,8,9,0},A1,1),""))))*1)


Please press Ctrl+Shift+Enter after pasting this formula as this is an array formula.


Drag the formula till the last cell of your data.


Also check below links for more understanding.


http://chandoo.org/wp/2012/06/19/extract-numbers-from-text-excel/


http://chandoo.org/forums/topic/delete-part-of-string-starting-with-small-letter


Please do let us know if this solve your problem.


Will wait for Ninjas to provide better, short and sweet formula.


Thanks & Regards,

Anupam Tiwari
 
Hi Suri M


Assuming your data is in A1:A2. In B1 and copy down this non array formula to extract the numbers.


=LOOKUP(20^20,1*MID(A1,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1)))))


Then if you require the text values, in C1 & copy down.


=SUBSTITUTE(A1,B1:B1,"")


Kevin
 
Hi Kevin,


You formula is awesome, working smoothly.


You are genius.


Thanks, I learned new formula today.


Thanks & Regards,

Anupam
 
Good day Kevin


Was just wondering how your formula would need to be altered if the string contained two sets of numbers say beginning and middle, beginning and end or middle and end.


just a wondering on my part so please do not waste too much of your time.
 
Good day Faseeh


To be honest both but I think that it would be the first that would educate more :)
 
@ bobhc


Perhaps something like this, in A1: BLR5658DTDC6785DDSWL


=SUM(MID(0&A1,LARGE(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)/1)*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^(ROW(INDIRECT("1:"&LEN(A1)))-1))


CTRL + SHIFT + ENTER
 
Hi, I have tweaked my previously mentioned formula as below and this is working fine with no error however will go with Kavin's lookup formula which is very nice one.


Considering that the value BLR5658DTDC is in cell A1.


=IFERROR(IFERROR(MID(A1&0,(MIN(IFERROR(SEARCH({1,2,3,4,5,6,7,8,9,0},A1,1),""))),(MIN(IFERROR(FIND(CHAR(ROW($A$65:$A$122)),A1,MIN(IFERROR(SEARCH({1,2,3,4,5,6,7,8,9,0},A1,1),""))),"")))-(MIN(IFERROR(SEARCH({1,2,3,4,5,6,7,8,9,0},A1,1),""))))*1,RIGHT(A1,(LEN(A1)-(MIN(IFERROR(SEARCH({1,2,3,4,5,6,7,8,9,0},A1,1),""))))+1)*1),"")


Press Ctrl+Shift+Enter.


@Kevin,


I have spent sometime to understand your formulas and now understood them well and liked your logic a lot.


Thanks & Regards,

Anupam Tiwari
 
You must log in or register to reply here.
Back
Top