Separate number from Text thru formula

[gov_007]

Hi All,


Its been a very long time I had encounterd with a new problem. I used to get raw data from our different vendors who used to entry datas in Excel. Datas like Customer Name, Phone numbers, Address and many other stuffs as well.


The problem what I am facing is they ( Vendor) used to send data like Name & Phone number in one cell, sometime unique Id & name in one cell e.g.


Roger9899000011

Roger 9899000011

4241Peter


If anyone can help me separating numbers and Names from one cell.


Thanks & Regards,

Abhinav

 

[Luke M]

If all the text/numbers are at least grouped together, I think we can do this with just formulas. Array formula (confirm using Ctrl+Shift+Enter, not just Enter) to extract the text:

=IF(MATCH(TRUE,CODE(MID(A2,ROW($1:$499),1))>57,0)=1,LEFT(A2,MATCH(TRUE,CODE(MID(A2,ROW($1:$499),1))<=57,0)-1),MID(A2,MATCH(TRUE,CODE(MID(A2,ROW($1:$499),1))>57,0),999))


Assuming previous formula is in B2, regular formula to get the number is:

=VALUE(IF(FIND(B2,A2)=1,MID(A2,LEN(B2)+1,999),LEFT(A2,LEN(A2)-LEN(B2))))

 

[gov_007]

Hi Luke M,


Thank you,


The above formula is working perfectly...but as this is the mega formula am little confused to understand it. And am afraid if i have to make certain changes while using this formula whether it will work or not.


Request you if u can illustrate the above both formulas as how it is working.Pls cc it abhinav.vivek@live.com


Thanks & Regards,

Abhinav

 

[Luke M]

Perhaps a couple of UDFs would be better to use. Easier to understand, and a little more versatile. To install, right click on sheet tab, view code, Insert - Module. Paste these in.

[pre]

Function GetText(r As String) As String
Dim xLetter As String
For i = 1 To Len(r)
xLetter = Mid(r, i, 1)
If UCase(xLetter) Like "[A-Z]" Then
GetText = GetText & xLetter
End If
Next i

End Function

Function GetNumber(r As String) As Double
Dim xLetter As String
For i = 1 To Len(r)
xLetter = Mid(r, i, 1)
If xLetter Like "[0-9]" Then
GetNumber = GetNumber * 10 + xLetter
End If
Next i
End Function

[/pre]
Close the VBE after pasting. In your workbook, simply use the formulas

=GetText(A2)

and

GetNumber(A2)

to extract the text and numbers.

 

[Sajan]

Hi Abhinav,

Assuming that the text and numbers always follow the convention you indicated, you could also try the following formula. (The formula assumes that your text/number is in cell A1).


=IF(ISNUMBER(LEFT(A1)*1), LEFT(A1,SUMPRODUCT(ISNUMBER(MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1)*1)*1)), RIGHT(A1,SUMPRODUCT(ISNUMBER(MID(A1,ROW($A$1:INDEX(A:A,LEN(A1))),1)*1)*1)))


Essentially, this counts the number of digits in cell A1, and then extracts the first set of characters or last set of characters, depending on whether the first char is a number or not.


The formula would support the use cases you outlined. It may not be the most efficient, but I will leave that to the experts on this forum to optimize!


Cheers,

Sajan.