I want to remove alphas from within a number.

[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?

[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/

[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))





"SakDaddy" wrote:

> 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?