Text to Columns based on character pattern

[reganshaw]

Hi All,

I have been given a data set that was poorly converted from a pdf file which I'm left trying to clean up.

All of the data is in column A and each line of data is in a single cell.
Example:

A1: A1A# ST. JOHN'S NL STN MAIN B1G DOMINION NS STN MAIN B3Z TANTALLON NS STN MAIN
A2: J1S WINDSOR QC SUCC BUREAU-CHEF J4K LONGUEUIL QC SUCC BUREAU-CHEF J7E BOISBRIAND QC SUCC DEPOT RIVE-NORD J1T ASBESTOS QC SUCC BUREAU-CHEF

I'd like to split the string in each cell at every occurrence of a letter+number+letter grouping. So A1 would be split into 3 columns;

• A1A# ST. JOHN'S NL STN MAIN
• B1G DOMINION NS STN MAIN
• B3Z TANTALLON NS STN MAIN

B1 would be split into 4 columns;

• J1S WINDSOR QC SUCC BUREAU-CHEF
• J4K LONGUEUIL QC SUCC BUREAU-CHEF
• J7E BOISBRIAND QC SUCC DEPOT RIVE-NORD
• J1T ASBESTOS QC SUCC BUREAU-CHEF

Any thoughts?

 

[jtakw]

Hi,

Are there possibly any other Numbers in between each Letter-Number-Letter ?

 

[reganshaw]

Each Letter-Number-Letter occurrence could contain any single digit number between 0 and 9.

 

[reganshaw]

Also... tried to update the original problem explanation but it didn’t post.

In the examples given, I incorrectly said cell B1 - I should have said cell A2.
All of the data is in column A.

 

[jtakw]

Hi,

Are there possibly any other Numbers in between each Letter-Number-Letter ?

Each Letter-Number-Letter occurrence could contain any single digit number between 0 and 9.

My question was, in between Each Letter-Number-Letter, is it possible there are other Numbers, like "A1A something 2 something else A2B"

Assuming there isn't, this Long formula will work, formula copied down and across as far as needed or until you see Blanks:

Book1
	A	B	C	D	E	F
1	A1A# ST. JOHN'S NL STN MAIN B1G DOMINION NS STN MAIN B3Z TANTALLON NS STN MAIN	A1A# ST. JOHN'S NL STN MAIN	B1G DOMINION NS STN MAIN	B3Z TANTALLON NS STN MAIN
2	J1S WINDSOR QC SUCC BUREAU-CHEF J4K LONGUEUIL QC SUCC BUREAU-CHEF J7E BOISBRIAND QC SUCC DEPOT RIVE-NORD J1T ASBESTOS QC SUCC BUREAU-CHEF	J1S WINDSOR QC SUCC BUREAU-CHEF	J4K LONGUEUIL QC SUCC BUREAU-CHEF	J7E BOISBRIAND QC SUCC DEPOT RIVE-NORD	J1T ASBESTOS QC SUCC BUREAU-CHEF
Sheet533

Cell Formulas
Range	Formula
B1	=IFERROR(MID($A1,FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1&"xx|","0","|"),"1","|"),"2","|"),"3","|"),"4","|"),"5","|"),"6","|"),"7","|"),"8","|"),"9","|"),"|","@",COLUMNS($B1:B1)))-1,FIND("^",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1&"xx|","0","|"),"1","|"),"2","|"),"3","|"),"4","|"),"5","|"),"6","|"),"7","|"),"8","|"),"9","|"),"|","^",COLUMNS($B1:B1)+1))-FIND("@",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1&"xx|","0","|"),"1","|"),"2","|"),"3","|"),"4","|"),"5","|"),"6","|"),"7","|"),"8","|"),"9","|"),"|","@",COLUMNS($B1:B1)))-1),"")

 

[reganshaw]

The formula seems to be exactly what I needed! Thanks for taking the time jtakw!

 

[jtakw]

You're welcome.