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
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
Microsoft Excel
Last Comment
Jim Metcalf
Use this formula first to replace multi space into single space =TRIM(SUBSTITUTE(A2,CHAR(3
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
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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 ;-)
ASKER
worked like a charm
thanks everyone
thanks everyone
ASKER
worked like a charm