Text to Columns

S

scojerroc

When I parse text, Excel trims the result. Is there a way to avoid this?

For example, I have a cell with "Master " and I want to split it into
"Master " and " ". When I try, it returns "Master" and "".

Any suggestions?
 
D

Dav

only by using a formula, it will always split on your first space if
that is the character you are spliting on. you could look at the mid
function combined with the search function to try and be more
specific.

You have not told us your logic in spliting so it is difficult to be
more helpful

Regards

Dav
 
W

Wiley

The parsing is not working because it is stripping trailing spaces. i don't
know how to stop iot other than fooling it. Try using a temp character.
There may be a better approach, but this will work if you don't get another
answer.

Before doing a text to columns, make a copy of the text in question. You
may need to modify the procedure some to meet your specific output
requirements.

1. Highlight the text in question.
2. Perform a Find and Replace. In the 'Find what' box, type 2 spaces. In
the 'Replace with' box type an uncommon chaaracter (one not likely to be in
your text - 2 times. I recommend "^^". ( I use 2 characters so that if it
does nto attempt to replace single spaces between words.)
3. Now do the text to columns using "Master^^^^" and "^^^^" for your
splits.
4. Now you have to remove your temp characters. In the 'Find what' box
type "^^", and in the 'Repalce with' box, type in 2 spaces. All should be
well.

Let us know how it worked out.
 
W

Wiley

Oops. I left out a step. After step 1, you should also format the cells as
"Text". Otherwise, your trailing spaces will still dissappear.
 
S

scojerroc

Sorry it took so long for me to reply. As to your answer, I've taken to
using F&R myself. Was hoping to avoid that, because often I'll get data
where that's not practical, being hard to find a character that does not
already exist within the data.

Thanks for the help, though.
 
Top