Extract Numbers From String
 

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!

Extract Numbers From String
 

BerkshireGuy wrote:
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

Extract Numbers From String
 

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


BerkshireGuy wrote:
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!

Extract Numbers From String
 

One mo

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 wrote:

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!

--

Dave Peterson