Home 
Search 
Today's Posts 
#1




Remove Numbers from Alphanumeric String
Hi,
I have a value in Cell A of ABC123. I want Cell B1 to contain the 123 from this cell. Can anyone tell me the formula to enter in B1. I'm sure I've seen it somewhere before but can't put my finger on it. Thanks! Dave 
#2




Remove Numbers from Alphanumeric String
if it's always the 4th position, then =mid(A1,4,255). If you want it to be
numeric, then =1*mid(a1,4,255). If the position is unknown but is always letters followed by numbers, ctrl+shift+enter this: =1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW($1:$20) ,1)),0),255) Bob Umlas Excel MVP "Dave" wrote in message ... Hi, I have a value in Cell A of ABC123. I want Cell B1 to contain the 123 from this cell. Can anyone tell me the formula to enter in B1. I'm sure I've seen it somewhere before but can't put my finger on it. Thanks! Dave 
#3




Remove Numbers from Alphanumeric String
Hi,
Try this: =RIGHT(A1,3) Thanks,  Farhad Hodjat "Dave" wrote: Hi, I have a value in Cell A of ABC123. I want Cell B1 to contain the 123 from this cell. Can anyone tell me the formula to enter in B1. I'm sure I've seen it somewhere before but can't put my finger on it. Thanks! Dave 
#4




Remove Numbers from Alphanumeric String
Try this UDF:
Function letteronly(r As Range) As String letteronly = "" If Application.WorksheetFunction.IsText(r.Value) Then s = r.Value For i = 49 To 57 s = Replace(s, Chr(i), "") Next i letteronly = s End If End Function  Gary''s Student  gsnu200751 "Dave" wrote: Hi, I have a value in Cell A of ABC123. I want Cell B1 to contain the 123 from this cell. Can anyone tell me the formula to enter in B1. I'm sure I've seen it somewhere before but can't put my finger on it. Thanks! Dave 
#5




Remove Numbers from Alphanumeric String
You can try this Custom Function. Copy the code into the VBA window of
your file and then go to cell B1 and type =RemoveTexts(A1) This function checks each character of your target cell and keeps only numeric characters. Before returning the number, it actually converts it to numeric (so 123 will be number, not text). For empty or text only cells, it will return zero. Public Function RemoveTexts(Target As Range) Dim t As String For i = 1 To Len(Target.Value) t = Mid(Target.Value, i, 1) If IsNumeric(t) = True Then RemoveTexts = RemoveTexts & t End If Next i RemoveTexts = Val(RemoveTexts) End Function On Oct 26, 9:03 pm, Dave wrote: Hi, I have a value in Cell A of ABC123. I want Cell B1 to contain the 123 from this cell. Can anyone tell me the formula to enter in B1. I'm sure I've seen it somewhere before but can't put my finger on it. Thanks! Dave 
#6




Remove Numbers from Alphanumeric String
Thanks Farhad. This works, but my string does not always have 3 digits. Some
are 4 or 5 digits. "Farhad" wrote: Hi, Try this: =RIGHT(A1,3) Thanks,  Farhad Hodjat "Dave" wrote: Hi, I have a value in Cell A of ABC123. I want Cell B1 to contain the 123 from this cell. Can anyone tell me the formula to enter in B1. I'm sure I've seen it somewhere before but can't put my finger on it. Thanks! Dave 
#7




Remove Numbers from Alphanumeric String
This returns #N/A
"Bob Umlas" wrote: if it's always the 4th position, then =mid(A1,4,255). If you want it to be numeric, then =1*mid(a1,4,255). If the position is unknown but is always letters followed by numbers, ctrl+shift+enter this: =1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW($1:$20) ,1)),0),255) Bob Umlas Excel MVP "Dave" wrote in message ... Hi, I have a value in Cell A of ABC123. I want Cell B1 to contain the 123 from this cell. Can anyone tell me the formula to enter in B1. I'm sure I've seen it somewhere before but can't put my finger on it. Thanks! Dave 
#8




Remove Numbers from Alphanumeric String
Hi GarysStudent. Can you explain how to use this? Thanks!
"Gary''s Student" wrote: Try this UDF: Function letteronly(r As Range) As String letteronly = "" If Application.WorksheetFunction.IsText(r.Value) Then s = r.Value For i = 49 To 57 s = Replace(s, Chr(i), "") Next i letteronly = s End If End Function  Gary''s Student  gsnu200751 "Dave" wrote: Hi, I have a value in Cell A of ABC123. I want Cell B1 to contain the 123 from this cell. Can anyone tell me the formula to enter in B1. I'm sure I've seen it somewhere before but can't put my finger on it. Thanks! Dave 
#9




Remove Numbers from Alphanumeric String
This doesn't seem to work.
"www.exciter.gr" wrote: You can try this Custom Function. Copy the code into the VBA window of your file and then go to cell B1 and type =RemoveTexts(A1) This function checks each character of your target cell and keeps only numeric characters. Before returning the number, it actually converts it to numeric (so 123 will be number, not text). For empty or text only cells, it will return zero. Public Function RemoveTexts(Target As Range) Dim t As String For i = 1 To Len(Target.Value) t = Mid(Target.Value, i, 1) If IsNumeric(t) = True Then RemoveTexts = RemoveTexts & t End If Next i RemoveTexts = Val(RemoveTexts) End Function On Oct 26, 9:03 pm, Dave wrote: Hi, I have a value in Cell A of ABC123. I want Cell B1 to contain the 123 from this cell. Can anyone tell me the formula to enter in B1. I'm sure I've seen it somewhere before but can't put my finger on it. Thanks! Dave 
#10




Remove Numbers from Alphanumeric String
Probably because you *didn't* enter it the proper way.
It's an *array* formula!  Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. You *must also* use CSE when revising the formula. You can click in the cell containing the formula. Then click in the formula bar, hold down <Ctrl and <Shift Then hit <Enter  HTH, RD  Please keep all correspondence within the NewsGroup, so all may benefit !  "Dave" wrote in message ... This returns #N/A "Bob Umlas" wrote: if it's always the 4th position, then =mid(A1,4,255). If you want it to be numeric, then =1*mid(a1,4,255). If the position is unknown but is always letters followed by numbers, ctrl+shift+enter this: =1*MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW($1:$20) ,1)),0),255) Bob Umlas Excel MVP "Dave" wrote in message ... Hi, I have a value in Cell A of ABC123. I want Cell B1 to contain the 123 from this cell. Can anyone tell me the formula to enter in B1. I'm sure I've seen it somewhere before but can't put my finger on it. Thanks! Dave 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Comparison of alphanumeric string  Excel Discussion (Misc queries)  
How to extract decimal numbers e.g. $1.57 from alphanumeric string  Excel Discussion (Misc queries)  
How do I replace last numeric string from a alphanumeric string?  Excel Discussion (Misc queries)  
Auto convert an alphanumeric string (CIS9638S) to numbers only?  Excel Worksheet Functions  
Auto convert an alphanumeric string (CIS9638S) to numbers only?  Excel Worksheet Functions 