# Remove letters and zeros from string

#### cvgreports

##### Board Regular
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. ### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Try this UDF (in a Standard module) -- In worksheet enter =num(A1)

Code:
``````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``````

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

Or

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

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.

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.

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

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

Code:
``````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)...

Code:
``````Function Num(inpt As String) As Variant
Num = Val(inpt)
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)...

Code:
``````Function Num(inpt As String) As Variant
Num = Val(inpt)
End Function``````

Nice use native vba functions.
Biz

Replies
12
Views
267
Replies
4
Views
230
Replies
4
Views
231
Replies
3
Views
301
Replies
6
Views
271

### Forum statistics

1,203,396
Messages
6,055,153
Members
444,766
Latest member
bryandaniel5 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back