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" & 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:
https://www.chicagocomputerclasses.com/excel-classes/