Removing text from cells leaving numbers (help with function)

[demonking]

I need a function that will remove all text from a cell and just leav
numbers. Formatting cells to number does not work.

For example if I have:
(Sired] Tennessee 37013 (herein

I just want 37013 left.

Anybody know a function to resolve this

 

[Tumbleweed]

The following will strip the text from the active cell and place the number
in the adjcent cell one column to the left. If there are subsequent numbers
in the original string you will get erroneous results. Put the cursor on the
cell to be processed and run the macro.
***********************************
Sub strip_text()

Dim sStartString As String
Dim sEndString As String
Dim iLength As Integer

sEndString = ""
sStartString = ActiveCell.Value
iLength = Len(sStartString)

For i = 1 To iLength
If IsNumeric(Mid(sStartString, i, 1)) Then
sEndString = sEndString & Mid(sStartString, i, 1)
End If
Next i

ActiveCell.Offset(0, -1).Value = sEndString

End Sub
*****************************************
37013 (Sired] Tennessee 37013 (herein
3013 (Sired] Kansas 3013 (herein
370155 (Sired] Washington 370155 (herein
99453123 (Sired] Tennessee 99453 (herein 123

*****************************************

 

[demonking]

I have literally 1000's of fields at a time to do this to. Is there
way to run this on multiple fields or to make it a function? I trie
making it a function but it does not work

 

[Gord Dibben]

Sub RemoveAlphas()
'' Remove alpha characters from a string.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub

Gord Dibben Excel MVP

 

[Peo Sjoblom]

Here's a formula that will strip out numbers provided they don't occur more
than once, i.e.

(Sired] Tennessee 37013 (herein


but not

(Sired] 12345 Tennessee 37013 (herein


array enter (ctrl + shift & enter)

=--MID(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1)),0),100-SUM
(--ISERROR(1*MID(A1,ROW(INDIRECT("1:100")),1))))

now copy the formula 1000 cells and while still selected copy the range, do
edit>paste special as values in place
delete the originals or better keep them just in case something went wrong
and copy and paste the numbers to another sheet


--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom