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?
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 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/
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?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks