Parsing text to columns

[basic]

Just started wondering if there's a simpler way to do this, I basically need to manually scrape information from text files and enter into excel. I've usually done it all by hand which takes a good long while.

Source plain-text input:
CUSTOMER NAME - [0a-00-3e-f5-2e-63] - LUID: 006
CUSTOMER NAME (CONTACT), (780) xxx-xxxx - [0a-00-3e-f6-ea-2a] - LUID: 009
CUSTOMER NAME (CONTACT) (780) xxx-xxxx - [0a-00-3e-f6-ea-30] - LUID: 007
CUSTOMER NAME - [0a-00-3e-f2-0c-f1] - LUID: 008
CUSTOMER NAME (Abbreviated) - [0a-00-3e-f7-4d-7e] - LUID: 010
CUSTOMER NAME (CUSTOMER SITE - NAME) - [0a-00-3e-d0-b5-de] - LUID: 003
CUSTOMER NAME, (780)xxx-xxxx cell. - [0a-00-3e-d0-b6-05] - LUID: 002
CUSTOMER NAME - [0a-00-3e-fa-81-45] - LUID: 005
CUSTOMER SITE - CUSTOMER NAME - [0a-00-3e-d0-b5-fe] - LUID: 004

Final Excel output after copying each item by hand ('|' is a separated column):
CUSTOMER NAME |0a-00-3e-f5-2e-63|
CUSTOMER NAME (CONTACT) |0a-00-3e-f6-ea-2a|
CUSTOMER NAME (CONTACT) |0a-00-3e-f6-ea-30|
CUSTOMER NAME |0a-00-3e-f2-0c-f1|
CUSTOMER NAME (Abbreviated) |0a-00-3e-f7-4d-7e|
CUSTOMER NAME (CUSTOMER SITE - NAME) |0a-00-3e-d0-b5-de|
CUSTOMER NAME |0a-00-3e-d0-b6-05|
CUSTOMER NAME |0a-00-3e-fa-81-45|
CUSTOMER SITE - CUSTOMER NAME |0a-00-3e-d0-b5-fe|

As you can imagine this is quite tedious after awhile.

It's the same thing I want to apply on each tab/sheet, but could always make it so its all in one sheet if that's easier. Any ideas?

[GaidenFocus]

i suggest going to the DATA tab and playing around with the TEXT TO COLUMNS feature.

[JBeaucaire]

Text To Columns one the way to go but your data doesn't seem to have a good consistent delimiter to use.

Put the data in column A.

Put this formula in B1:
=LEFT(A1,FIND("[", A1)-4)

Put this in C1:
=MID(A1, FIND("[", A1) +1, 17)

Drag those two formulas down. Is that usable?

[basic]

Awesome! is there anyway I can get rid of the source column A and just keep the accurate B & C columns?

[Leith Ross]

Hello basic,

This macro will separate the data into 2 columns: A and B. The example workbook has a button on "Sheet1" to run the macro.

'Thread: http://www.excelforum.com/excel-programming/755186-parsing-text-to-columns.html
'Poster: basic
'Written: December 01, 2010
'Author:  Leith Ross (www.excelforum.com & www.thecodecage.com)

Sub ParseData()

  Dim Cell As Range
  Dim Rng As Range
  Dim RegExp As Object
  Dim S As String
  Dim T As Boolean
  Dim X As String
  
    Set Rng = Worksheets("Sheet1").Range("A1:A9")
    
    Set RegExp = CreateObject("VBScript.RegExp")
    RegExp.IgnoreCase = True
    RegExp.Pattern = "([\w\-\s\(]+)(?:\)|\,|\s\-).+\[(.+)\].*"
    
      For Each Cell In Rng
        S = Cell.Value
        If RegExp.Test(S) Then
          X = RegExp.Replace(S, "$1")
            If InStr(1, X, "(") Then Cell.Value = X & ")" Else Cell.Value = X
          Cell.Offset(0, 1).Value = RegExp.Replace(S, "$2")
        End If
      Next Cell
      
    Rng.Resize(ColumnSize:=2).EntireColumn.AutoFit
    
End Sub

[JBeaucaire]

Awesome! is there anyway I can get rid of the source column A and just keep the accurate B & C columns?

Yes, after dragging down your formulas, highlight columns B and C, copy, then use Edit > paste special > Values to remove the formulas. Then you can delete column A.

[basic]

Wow! look how that works... I also like the solution Leith Ross has but I found out it doesn't co-operate with the & sign well like CUSTOMER NAME & CUSTOMER NAME. Also for some reason the macro script only parses the first 9 values. JBeaucaire's solution is also good I can't see a problem with it.

This is cool I can start flying through this stuff now ^_^