Extract Numbers From String

[BerkshireGuy]

I have a field formatted like the following:

2340/LEFT EYE BLINDNESS, 2341/CAP PRES-
2340/LEFT EYE BLINDNESS, 2341/CAP PRES-
2340/LEFT EYE BLINDNESS, 2341/CAP PRES-
2112/M&N
2112 MENTAL/NERVOUS, 2801 ASTHMA_______
2112 MENTAL/NERVOUS, 2801 ASTHMA_______
2112 MENTAL/NERVOUS, 2801 ASTHMA_______
2112 MENTAL/NERVOUS, 2801 ASTHMA_______
REIS:ADD 2960/THORACIC, 2918/EITHER OR
REIS:ADD 2960/THORACIC, 2918/EITHER OR
REIS:ADD 2960/THORACIC, 2918/EITHER OR
REIS:ADD 2960/THORACIC, 2918/EITHER OR

Is there a way to extact just the numbers and if there is more than one
number, seperate it by a comma? Note: Each of the numbers are 4 digits
in length.

ex - first row should read 2340, 2341

TIA!

 

[Ken Johnson]

I have a field formatted like the following:

2340/LEFT EYE BLINDNESS, 2341/CAP PRES-
2340/LEFT EYE BLINDNESS, 2341/CAP PRES-
2340/LEFT EYE BLINDNESS, 2341/CAP PRES-
2112/M&N
2112 MENTAL/NERVOUS, 2801 ASTHMA_______
2112 MENTAL/NERVOUS, 2801 ASTHMA_______
2112 MENTAL/NERVOUS, 2801 ASTHMA_______
2112 MENTAL/NERVOUS, 2801 ASTHMA_______
REIS:ADD 2960/THORACIC, 2918/EITHER OR
REIS:ADD 2960/THORACIC, 2918/EITHER OR
REIS:ADD 2960/THORACIC, 2918/EITHER OR
REIS:ADD 2960/THORACIC, 2918/EITHER OR

Is there a way to extact just the numbers and if there is more than one
number, seperate it by a comma? Note: Each of the numbers are 4 digits
in length.

ex - first row should read 2340, 2341

TIA!

Hi,

Try this user defined function (requires XL2000 or higher)...

Public Function Numbers(Cell As String) As String
Cell = WorksheetFunction.Substitute(Cell, "/", " ")
Dim StrArray As Variant, I As Long
StrArray = Split(Cell)
For I = 0 To UBound(StrArray)
If IsNumeric(StrArray(I)) Then
Numbers = Numbers & StrArray(I) & ", "
End If
Next I
If Len(Numbers) > 2 Then
Numbers = Left(Numbers, Len(Numbers) - 2)
End If
End Function

Ken Johnson

 

[John Coleman]

Try

Function ExtractNums(ByVal InString As String) As Variant
Dim i As Long
Dim A As Variant
Dim ch As String
For i = 1 To Len(InString)
ch = Mid(InString, i, 1)
If Asc(ch) < 48 Or Asc(ch) > 57 Then
Mid(InString, i, 1) = ","
End If
Next i
Do While InStr(InString, ",,") <> 0
InString = Replace(InString, ",,", ",")
Loop
If InString Like "*," Then
InString = Mid(InString, 1, Len(InString) - 1)
End If
If InString Like ",*" Then
InString = Mid(InString, 2)
End If
ExtractNums = Replace(InString, ",", ", ")
End Function

 

[Dave Peterson]

One more:

Option Explicit
Function JustNumbers(rng As Range)

Dim iCtr As Long
Dim myStr As String

myStr = rng.Cells(1).Text

For iCtr = 1 To Len(myStr)
If IsNumeric(Mid(myStr, iCtr, 1)) Then
'do nothing
Else
Mid(myStr, iCtr, 1) = " "
End If
Next iCtr

With Application
myStr = .Substitute(.Trim(myStr), " ", ", ")
End With

JustNumbers = myStr

End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.
Into a test cell and type:
=JustNumbers(a1)

And it won't care how many consecutive digits there are in any number.

 

[BerkshireGuy]

These are great! Thanks.

Now that I think of it, it would be REALLY cool if I could but each of
the extracted numbers in another worksheet, which I can then do a pivot