View Single Post
  #13   Report Post  
Posted to microsoft.public.excel.misc
www.exciter.gr: Custom Excel Applications! www.exciter.gr: Custom Excel Applications! is offline
Banned
 
Posts: 19
Default Remove Numbers from Alphanumeric String

Dave
just tried my solution again and it works fine.

Please follow the procedure step by step to make it work for you too:

1. Open your excel file (suppose its name is yourfile.xls)
2. Go to Tools/Macro/Visual Basic Editor
3. Right click on the line VBAProject(yourfile.xls) on the left list
and click on Insert Module
4. Paste the code into the white window on the right:
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

5. Go back to your excel sheet, type ABC123 or anything else in Cell
A1
6. Type the formula: "=RemoveTexts(A1)" without the quotes into Cell
B1 and press enter
7. It should return the correct result

Good luck!



On Oct 28, 7:04 pm, Dave wrote:
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- Hide quoted text -


- Show quoted text -