Splitting partial cell contents with a macro

[Jason Excel]

Hi all, building a spreadsheet with macros since I will be doing this with hundreds of worksheets over the next year. I am copying and pasting data from a PDF and using the macros to edit out all of the information I don't need. This forum has already been instrumental in getting to where I am, which is almost complete. I cannot figure this part out though, I have tried recording, searching here, etc. but nothing is working for this particular situation.

I have Column A containing data in this form, examples:
2 Preferred Cash Customer 11 0 1 0
3 Farm Use Account 10 0 0
701 Jimmy Barnes 1 0 72 1
1006 KS Dept. Of Transportation 5 0 1
24560 First National Bank of Missouri 46 9

I need the data broken into cells A, B, C, examples:
2 Preferred Cash Customer 11
701 Jimmy Barnes 1
24560 First National Bank of Missouri 46

More info on desired output:
Column A contains the number preceding the text, this number varies in length from 1 to 10 digits. I thought the first space would be a good delimiter.
Column B contains the text, it varies in length and the number of words.
Column C contains the number immediately after the text and everything to the right is deleted. As you can see, the quantity of numbers after the text is variable but I only need the first one.
The range of rows will vary by each spreadsheet so it should look at the entire column.

Thank you so much for any help you can provide. I look forward to learning more about excel.

 

[chicagocomputerclasses]

This should do it for you. Place your data in column A and run the macro.
The new data will be placed in columns B, C & D

Sub BreakTextNum()
lr = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
    For Each cell In Range("a1:a" & lr)
    
    'grab teh number
    num = Left(cell, InStr(cell, " ") - 1)
    
    'grab teh text
        For i = 1 To Len(cell)
            Dim currentCharacter As String
            currentCharacter = Mid(cell, InStr(cell, " ") + i, 1)
            If IsNumeric(currentCharacter) = True Then
                GetPositionOfFirstNumericCharacter = i
                Exit For
            End If
        Next i
    
    txt = Mid(cell, InStr(cell, " ") + 1, i - 2)
    'grab teh after number
    lasti = i + InStr(cell, " ") - 2
    anum = Mid(cell, lasti + 2, InStr(lasti + 2, cell, " ") - lasti - 2)
    
    
    Cells(cell.Row, 2) = num
    Cells(cell.Row, 3) = txt
    Cells(cell.Row, 4) = anum
    
    Next cell


End Sub

 

[Rick Rothstein]

Here is another macro that you can try (it should be faster than the other code submitted so far)...

Sub SplitDataIntoThreeCells()
  Dim R As Long, X As Long, Data As Variant, Result As Variant, Parts() As String
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  ReDim Result(1 To UBound(Data), 1 To 3)
  For R = 1 To UBound(Data)
    Parts = Split(Data(R, 1))
    Result(R, 1) = Parts(0)
    For X = 1 To UBound(Parts)
      If Not Parts(X) Like "*[!0-9]*" Then
        Result(R, 3) = Parts(X)
        Exit For
      Else
        Result(R, 2) = Result(R, 2) & " " & Parts(X)
      End If
    Next
  Next
  Range("A1").Resize(UBound(Result), 3) = Result
End Sub

 

[Jason Excel]

Both of those functioned perfectly, thank you.

I am going over the code to develop my understanding and having two examples is even better to do so.