Thread: Parsing text to columns

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?

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?

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

Originally Posted by basic

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.