I want to remove alphas from within a number.
 

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?

I want to remove alphas from within a number.
 

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/


--
mrice
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=532039

I want to remove alphas from within a number.
 

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?