MrExcel Publishing
Your One Stop for Excel Tips & Solutions

sorting text data


Posted by Oskar Quieto on October 17, 2001 3:35 AM

How can I force Excel (97) to sort via a strictly alpabetical order, IGNORING blank spaces within cell enteries? Example : "A CAR" sorts earlier than "ATTENTION" due to the blank character between the A and C. Is there any way around this? I tried entering a strictly alphanumeric custom sort, but blank characters still took precedent (as did "." BTW). Any help wpuld be greatly appreciated...


Posted by Juan Pablo on October 17, 2001 7:16 AM

I would use a new column, using =SUBSTITUTE(A2," ","") if your data is in A2, copy as needed and sort using THIS column.

Juan Pablo

Posted by Oskar Quieto on October 17, 2001 11:13 AM

Great! But is there anyway to have multiple substitutions take place at once (such as stripping away all *,#,^ etc as well as all empty spaces)?


Posted by Oskar Quieto on October 17, 2001 11:41 AM

nevermind - nested functions :] Thanks for all your help...