Link to home
Create AccountLog in
Avatar of Jim Metcalf
Jim MetcalfFlag for United States of America

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
Avatar of Professor J
Professor J

Use this formula first to replace multi space into single space =TRIM(SUBSTITUTE(A2,CHAR(32)," "))
Avatar of 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
Avatar of aikimark
aikimark
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
+1  aikimark  very good approach with regex
Thanks, Prof.  A click on the Good Comment link will also be helpful.
:-)   just clicked on the "Good Comment"   it is worth 10 clicks, but i just can only click once ;-)
worked like a charm
thanks everyone
worked like a charm