Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2nd attempt ~ complicated formula | Excel Worksheet Functions | |||
how do i remove telephone number hyphens from a list in Excel? | Excel Discussion (Misc queries) | |||
Number of labels on X-axis one more than number of values on Y-axi | Charts and Charting in Excel | |||
duplicate number | Excel Discussion (Misc queries) | |||
Where is DateDiff function in Excel 2002 ? | Excel Worksheet Functions |