Solved: Text to Columns

[austenr]

I the attached workbook I need to break out the name in a text string and make it into a list. I have approximately 500000 lines of this to do so a macro solution would be ideal. Any thoughts or solutions appreciated. Thanks in advance.

The first part of sheet one is the before picture and the bottom section is what I would like to end up with.

[patel]

can you attach a file with more data ? at least 5 lines

[austenr]

HERE YOU ARE.

[Paul_Hossler]

Something like this might be a starting point

There are some assumptions as to the data and where to put it, but they can be changed easily



Option Explicit

Sub BreakoutData()
Dim iIn As Long, iMatch As Long
Application.ScreenUpdating = False
With ActiveSheet
For iIn = 1 To .Cells(1, 1).CurrentRegion.Rows.Count
If Len(Trim(.Cells(iIn, 1).Value)) = 0 Then
.Cells(iIn, 2).Value = Trim(.Cells(iIn, 2).Value)
iMatch = InStr(.Cells(iIn, 2).Value, ".")
If iMatch > 0 Then
.Cells(iIn, 3).Value = Left(.Cells(iIn, 2).Value, iMatch - 1)
End If
Else
.Cells(iIn, 3).Value = .Cells(iIn, 1).Value
End If
Next iIn
End With
Application.ScreenUpdating = False
End Sub


Paul

[austenr]

Works fine for the first group but how can I get it to do the whole sheet?

[Paul_Hossler]

Sorry, I was going by your Sheet1 example from your first post

This gets the first part of any col B data and puts it in the same row in Col A



Sub BreakoutData()
Dim iIn As Long, iMatch As Long, iLast As Long
Application.ScreenUpdating = False
With ActiveSheet
iLast = .Cells(.Rows.Count, 2).End(xlUp).Row
For iIn = 1 To iLast
If Len(Trim(.Cells(iIn, 2).Value)) > 0 Then
.Cells(iIn, 2).Value = Trim(.Cells(iIn, 2).Value)
iMatch = InStr(.Cells(iIn, 2).Value, ".")
If iMatch > 0 Then
.Cells(iIn, 1).Value = Left(.Cells(iIn, 2).Value, iMatch - 1)
End If
End If
Next iIn
End With
Application.ScreenUpdating = False
End Sub


Look at 'After' and see if that's what you were looking for


Paul

[austenr]

Thanks, thats what I was after. Solved.

[Paul_Hossler]

C/P oops -- The last line should be ...



Application.ScreenUpdating = True


Paul