I want to remove alphas from within a number.

S

SakDaddy

If I have a column of numbers that have 123a4b5c and I want to remove the
"abc" so that the change reads 12345, what is the function I use?
 
M

mrice

I would suggest a user defined function which sequentially substitutes
that characters with nothing.


Function RemoveAlphas(Cell)
TestString = Cell.Value
For N = Len(Cell) To 1 Step -1
If Asc(Mid(TestString, N, 1)) < 48 Or Asc(Mid(TestString, N, 1)) >
57 Then
TestString = Left(TestString, N - 1) & Right(TestString,
Len(TestString) - N)
End If
Next N
RemoveAlphas = TestString
End Function

Martin

http://homepage.ntlworld.com/martin.rice1/
 
D

Duke Carey

Are all your text values laid out the same:

3 digits, 1 alpha, 1 digit, 1 alpha, 1 digit, 1 alpha?

If so, then use

=value(left("123a4b5c",3)&mid("123a4b5c" 5,1)&midmid("123a4b5c" 7,1))
 
Top