asked on

text to columns problem

I need to format a text to column. the problem is... i am unable to find a consistent identifier between the two columns except for.

More than one space.........

in column a, you will see that i can't use spaces because some of the addresses contain more than one space and i can't use fixed width because some of the addresses flow into where the name would be of the customer in the other rows...

i do normally see "2 or more spaces" between the desired column contents. does anyone know how to do this.
experts-exchange-problem-2.xlsx

Professor J

Use this formula first to replace multi space into single space =TRIM(SUBSTITUTE(A2,CHAR(32)," "))

Jim Metcalf

ASKER

question JimJam

if i do that... the addresses with a different number of spaces will become muddied with the customer name...
am i correct

example see how the first line will have 4 spaces and then the customer name while the 2nd will have 3 spaces and then the customer name

124 main st south lamb, jason 124 main st south lamb,jason
100 elm st smith, mary 100 elm st smith, mary

ASKER CERTIFIED SOLUTION

aikimark

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

Open in Notepad or similar and do a find and replace for double space and replace with something unique eg pipe symbol ( shift plus key between z and shift). Then the ttc will work using the pipe as the delimiter.

Professor J

+1 aikimark very good approach with regex

aikimark

Thanks, Prof. A click on the Good Comment link will also be helpful.

Professor J

:-) just clicked on the "Good Comment" it is worth 10 clicks, but i just can only click once ;-)

Jim Metcalf

ASKER

worked like a charm
thanks everyone

Jim Metcalf

ASKER

worked like a charm