Remove letters and zeros from string

[cvgreports]

I have a list of about 1,400 numbers ranging in different lengths. What I'm trying to accomplish with a formula is to remove the single letter at the end of each number (not all numbers have them) and in addition to removing all zeros at the beginning of the number (again not all numbers have them).

I hope that made sense. Here is an example...say I have the following 5 numbers:

8014554
45678456
87451245
0008014554b
0008014554c


And what I need my formula to do provide is the following results instead:

8014554
45678456
87451245
8014554
8014554


So far I have started with the following =LEFT(A1,LEN(A1)-1), but that will always remove the last character (number and letter). And I don't know what do next, so I'm lost.

Thanks in advance.

 

[jim may]

Try this UDF (in a Standard module) -- In worksheet enter =num(A1)

Function num(inpt As String)
     Dim i As Integer
     For i = 1 To Len(inpt)
         If IsNumeric(Mid(inpt, i, 1)) Then
             num = num & Mid(inpt, i, 1)
         End If
     Next i
     num = CLng(num)
End Function

 

[Joseph.Marro]

cvgreports,

If you want a formula solution, the following will accomplish the task you described. The formula assumes that there will only be one alpha character at the end of the number and that no alpha characters will be in the middle.

=IF(ISERROR(RIGHT(A1,1)*1)=TRUE,LEFT(A1,LEN(A1)-1)*1,A1*1)

If the task is more complex, I could modify the formula to account for the above assumptions.

Thank you,

Joseph Marro

 

[ScottR]

Or

=IF(ISNUMBER(A1),INT(A1),INT(LEFT(A1,LEN(A1)-1)))

 

[cvgreports]

Thank you all. jim may, I'm a rookie when it comes to VBA so I don't know to use that code.

Joseph.Marro & ScottR I tried both formulas and on the first number I used it on I get a #VALUE error. It's a number with no zeros or a letter at the end and the result of the formula should be just the number.

 

[cvgreports]

please disregard my last post I don't know what I was doing wrong but it works, every number will only have one alpha charater, but what if there is a dash before the letter? How can I remove that as well if there is a dash and a letter.

 

[Joseph.Marro]

cvgreports,

Give this a try.

=IF(ISERROR(RIGHT(A1,1)*1)=TRUE,INT(SUBSTITUTE(LEFT(A1,LEN(A1)-1),"-","")),INT(SUBSTITUTE(A1,"-","")))

Thank you,

Joseph Marro

 

[Rick Rothstein]

Try this UDF (in a Standard module) -- In worksheet enter =num(A1)

Function num(inpt As String)
     Dim i As Integer
     For i = 1 To Len(inpt)
         If IsNumeric(Mid(inpt, i, 1)) Then
             num = num & Mid(inpt, i, 1)
         End If
     Next i
     num = CLng(num)
End Function

Jim, I think this one-liner will do exactly what your function does (at least for the OP's data as described to us)...

Function Num(inpt As String) As Variant
  Num = Val(inpt)
End Function

 

[Biz]

Jim, I think this one-liner will do exactly what your function does (at least for the OP's data as described to us)...

Function Num(inpt As String) As Variant
  Num = Val(inpt)
End Function

Nice use native vba functions.
Biz