Then you didn't enter the formula and/or the code correctly. Using the example I posted for extraction from the string in A2 do the following:
1. Select cell B2 with your mouse.
2. Hold down the left mouse key and continue the selection by moving the mouse right until the range B2:I2 is selected, with B2 active.
3. Type: =ExtractNumsToCells(A2) - you should see this function in the list of possible functions provided. If not you haven't put the code in the right place.
4. Press Ctrl+Shift+Enter down together.
Here's how to put the code in your workbook:
To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.
And if you prefer to not have the .00 in the output, here's a revised code to handle that:
Code:
Function ExtractNumsToCells(ByVal S As String) As Variant
Dim Patt As Variant, Vout
Patt = "[0-9]+\.?\d{0,2}"
If S = "" Then
ExtractNumsToCells = ""
Exit Function
End If
With CreateObject("VBScript.regexp")
.Global = True
.Pattern = Patt
If .test(S) Then
For Each Match In .Execute(S)
Vout = Vout & " " & Format(Match, "0")
Next Match
End If
ExtractNumsToCells = Split(Right(Vout, Len(Vout) - 1), " ")
End With
End Function
