Separating information in the same cell

**KsuGuy26** · 02-14-2012, 06:32 PM

I have a about 20,000 rows of information that read like:

A1 A & E ELECTRICAL SERVICES INC 740-927-3421 51 BEECHWOOD DR SW
B1 REYNOLDSBURG OH 43068
C1 3/31/2012

I need them to read
A1 A & E ELECTRICAL SERVICES INC
A2 740-927-3421
A3 BEECHWOOD DR SW
A4 REYNOLDSBURG
A5 OH
A6 43068

If we can get them to:
A1 A & E ELECTRICAL SERVICES INC
A2 740-927-3421
A3 BEECHWOOD DR SW
B1 REYNOLDSBURG
B2 OH
B3 43068

I can take care of the rest. I have tried a number of things but every option requires me to go through by hand and edit thousands of cells of information.

Additionally is there a good way to delete entire rows that are blank or has page 1, page 2, etc.

Any help would be really appreciate!!!

Ben

**tigeravatar** · 02-14-2012, 06:55 PM

KsuGuy26,

Welcome to the forum!

Attached is an example workbook based on the criteria provided. It uses the A1:A3, B1:B3 model. Row 1 has headers, so I put the original data in cells A2:A4 and extracted data parts 1-3 in B2:B4, extracted data parts 4-6 in C2:C4. I figured this would be easiest for copying the formulas down. It's important to note that extracting the phone number (B3) relies on the phone number always being 10 digits, and always being seperated by hyphens.

In B2: =TRIM(LEFT(SUBSTITUTE(A2,B3,REPT(" ",255)),255))

In B3: =TEXT(LOOKUP(2^999,--MID(SUBSTITUTE(SUBSTITUTE(A2," ","x"),"-",""),ROW($A$1:INDEX(A:A,LEN(A2)-2)),10)),"000-000-0000")

In B4: =TRIM(RIGHT(SUBSTITUTE(A2,B3,REPT(" ",255)),255))

In C2: =TRIM(SUBSTITUTE(SUBSTITUTE(A3,C3,""),C4,""))

In C3: =TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(A3," "&C4,"")," ",REPT(" ",99)),99))

In C4: =--TRIM(RIGHT(SUBSTITUTE(A3," ",REPT(" ",99)),99))

As for deleting all blank rows, or all rows with specific criteria, take a look at AutoFilter.