Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns
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 -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=563861 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns
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. "scojerroc" wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns
Oops. I left out a step. After step 1, you should also format the cells as
"Text". Otherwise, your trailing spaces will still dissappear. "Wiley" wrote: 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. "scojerroc" wrote: 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Text to Columns
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. "Wiley" wrote: Oops. I left out a step. After step 1, you should also format the cells as "Text". Otherwise, your trailing spaces will still dissappear. "Wiley" wrote: 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. "scojerroc" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I unwrap text to columns? | Excel Discussion (Misc queries) | |||
Text to columns - one way street? | Excel Discussion (Misc queries) | |||
How do I convert from text to columns automatically on import? | Excel Discussion (Misc queries) | |||
comparing columns of text (cross-searching) | Excel Discussion (Misc queries) | |||
Text to columns | Excel Discussion (Misc queries) |