Identifying numbers from letters

[Helen]

If I have a unique part number such as 531916300WZ or 5369445Z, is there a formula that would just give me the numbers? The LEN function would only work if it was the same length, but they're all different.

I have no macro knowledge, so a formula if there is one would be great!

Thanks
Helen

 

[Aladin Akyurek]

Are the letters always at the end, following the pattern:

DDD...LLL..

where D stands for a digit and L for a letter?

 

[BrianB]

Assuming that your data always has letters at the end :-
In a macro sheet
'-------------------------------------
Public Function GetNum(S As String)
'- find first letter and split string
For n = 1 To Len(S)
If Asc(Mid(S, n, 1)) > 57 Then
GetNum = Left(S, n - 1)
Exit Function
End If
Next
End Function
'---------------------------------------

Use a formula like this in the worksheet
=getnum(A1)

 

[CraigM]

Or if the letters are anywhere within the string:

Function NumbersOnly(sString As String)
Num = ""
For n = 1 To Len(sString)
If Asc(Mid(sString, n, 1)) >= 48 And Asc(Mid(sString, n, 1)) <= 57 Then
Num = Num & Mid(sString, n, 1)
End If
Next
NumbersOnly = Num
End Function

 

[SIXTH SENSE]

hi!
This removes all the leters in in a combination of letters and numbers.

this assumes that they are found in column A.

try this one



<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> RemoveLetters()
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cl <SPAN style="color:#00007F">In</SPAN> Range("A1:a" & Range("a65536").End(xlUp).Row)
        tmp = cl.Value
        cl.Value = ""
        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> Len(tmp)
            <SPAN style="color:#00007F">If</SPAN> IsNumeric(Mid(tmp, i, 1)) <SPAN style="color:#00007F">Then</SPAN>
               cl.Value = cl.Value & Mid(tmp, i, 1)
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> i
    <SPAN style="color:#00007F">Next</SPAN> cl
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Aladin Akyurek]

Geez. All that VBA...

 

[XL-Dennis]

Aladin,

I have no macro knowledge, so a formula if there is one would be great!

I do agree with You although I'm a VBA-helper on this board :wink:

At least I try to avoid to apply VBA where it is obvious that there might exist a formula-solution and/or that OP clearly ask for a formula.

Hey man - When will You "convert" to the general trend to apply VBA on everything

Take care and keep up the good function/formula-approach!
Dennis

 

[tusharm]

It would help if you could clarify the problem as requested by Aladin. However, if you have the functions at http://www.tmehta.com/regexp/add_code.htm installed, you could use =RegExpSubstitute(A36,"[^\d]","") to remove all non-digits irrespective of their location.

If I have a unique part number such as 531916300WZ or 5369445Z, is there a formula that would just give me the numbers? The LEN function would only work if it was the same length, but they're all different.

I have no macro knowledge, so a formula if there is one would be great!

Thanks
Helen

 

[Helen]

Hello all,

Thanks to all who have been helping!

Aladin, in reply to your question, yes they wil have the pattern of DD..LL.

Tusharm, I have been onto the site you suggested www.tmehta.com/regexp/add_code.htm, and am confused as it sounded like I'd have to actually install something, whereas there is just a code on this page? Otherwise the formula RegExpSubstitute sounds like a useful formula!

Thanks again to all for helping

Helen
ps - Xl-Dennis, fancy coming to England to teach me VBA?!!!

 

[Aladin Akyurek]

...Aladin, in reply to your question, yes they wil have the pattern of DD..LL.
...

aaREMOVER NumPart.xls
	A	B	C	D
1	3205624z	3205624
2	12345	12345
3	231ret	231
4
Helen

The formula in B1 is:

=--SUBSTITUTE(A1,SUBSTITUTE(A1,LEFT(A1,SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{".",0,1,2,3,4,5,6,7,8,9},""))))),""),"")