Parsing text to columns

**basic** · 12-01-2010, 06:09 PM

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** · 12-01-2010, 06:44 PM

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

**JBeaucaire** · 12-01-2010, 06:58 PM

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** · 12-01-2010, 07:33 PM

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

**Leith Ross** · 12-02-2010, 02:07 AM

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.

Please Login or Register  to view this content.

**JBeaucaire** · 12-02-2010, 10:43 AM

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.

**basic** · 12-02-2010, 01:46 PM

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 ^_^