VBA - Get First Number & Then Text & Following Number - Excel Macro

This Excel Macro will transform the text data placed in column A and Break it down into 3 Columns.

Data Format Required:
15654 Text goes Here 16 2 6 1595
1 Different Text 1 6
number (space) text of any lenght (space) number (space) other characters

Output will return:

First column: full first number
Second column: full text string in the middle of surrounded numbers
Third Column: first full number after text

Sub BreakTextNum()
lr = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
    For Each cell In Range("a1:a" &amp; 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

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

Posted by Excel Instructor:
http://www.houstoncomputerclasses.com/excel-classes/

VBA – Get First Number & Then Text & Following Number – Excel Macro

Locations: