Delete numeric part of a string

  • Thread starter Thread starter Terry
  • Start date Start date
T

Terry

I have a column with has data which is a mix of numeric and alphabetic
characters.
Can any suggest a formula which would return only the alphabetic part of the
string.
I don't want the 0-9 numerics.

Terry
 
I have a column with has data which is a mix of numeric and alphabetic
characters.
Can any suggest a formula which would return only the alphabetic part of the
string.
I don't want the 0-9 numerics.

Terry
Click to expand...

You could use a User Defined Function:

========================
Function GetLtrs(str) As String

GetLtrs = ""

Dim N As Integer
For N = 1 To Len(str)
If Not (IsNumeric(Mid(str, N, 1))) Then GetLtrs = GetLtrs & Mid(str, N,1)
Next
End Function
===========================

To enter this, <alt><F11> opens the VB editor
Ensure your project is highlighted in the Project Explorer, then Insert/Module
and paste the above code into the window that opens.

Return to your worksheet. The formula =GetLtrs(A1) will return only the
letters in the string you have in A1.


--ron
 
One (long) way:

1. Assuming your strings are in column A, press Ctrl+F3,
create the name "nonum" (w/out quotes) and use the
formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
($A1,0,""),1,""),2,""),3,""),4,"")

2. Now use this formula on your worksheet, starting in
row 1:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
(nonum,5,""),6,""),7,""),8,""),9,"")

HTH
Jason
Atlanta, GA
 
Are the numbers always in the same position of the string such as ABC01,
ABC02, ABC03 or 11ABC, 22ABC, 33ABC or ABC12XYZ, AVX43WQI? If so, you can
extract using right, mid and left functions. If the numbers are in random
locations that'd be a bit trickier. Could you provide some examples?

- John
www.JohnMichl.com
 
Hi Terry,

Are they always grouped (eg. abc1234def, abcdef123, 12abcdef) in which case
you can use this array formula (hold Ctrl+Shift while you press Enter)

=LEFT($A1,MATCH(0,0*MID($A1,COLUMN(1:1),1),0)-1)&
RIGHT($A1,LEN($A1)-MATCH(0,0*MID($A1,COLUMN(1:1),1),1))

Or, are numeric characters scattered amongst other characters (eg. a1bc2d3,
ab12cd34, etc) in which case Jason's reply is probably the best solution.

Steve D.
 
Back
Top