Convert Text String To Numbers

[paulkc]

I have a spreadsheet containing several thousand records. I am trying to extract certain parts of a certain field. This field contains a text string that might be something like "001-018" or "003". I want to extract into 2 different columns the first number and the last number (if there is one). If column A contains the "001-018" then I want column B to have "1" and column C to have "18". If column A contains "003" then column B would have "3" and column C would be left blank. I can't figure out the correct formulas to do this. Thanks for the help.

 

[hiker95]

paulkc,


Sample raw data before the macro:

Excel Workbook
	C	D	E
1	10~
2	12~
3	17~27
4	31/`34
5	1_5
6	12
7	001-003
8	003-012
9
Sheet1

After the macro:

Excel Workbook
	C	D	E
1	10~	10
2	12~	12
3	17~27	17	27
4	31/`34	31	34
5	1_5	1	5
6	12	12
7	001-003	1	3
8	003-012	3	12
9
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ExtractNumbers()
' hiker95, 05/04/2011
' http://www.mrexcel.com/forum/showthread.php?t=547466
Dim c As Range, H As String, a As Long, HH As String, NC As Long
Application.ScreenUpdating = False
For Each c In Range("C1", Range("C" & Rows.Count).End(xlUp))
  NC = 3: HH = ""
  H = Trim(c)
  For a = 1 To Len(H)
    If IsNumeric(Mid(H, a, 1)) Then
      HH = HH & Mid(H, a, 1)
    ElseIf HH <> "" Then
      NC = NC + 1
      Cells(c.Row, NC) = CLng(HH)
      HH = ""
    End If
  Next a
  If HH <> "" Then
    NC = NC + 1
    Cells(c.Row, NC) = CLng(HH)
  End If
Next c
Application.ScreenUpdating = True
End Sub

Then run the ExtractNumbers macro.

 

[paulkc]

That worked perfectly, thanks for your help.