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...
