Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
merging spreadsheets/one has extra space in each cell
I have several spreadsheets that contain customer information, ie. name
address, etc. These spreadsheets have been sent to us by the manufacturer we represent. One group of spreadsheets has a space before the text in every cell, while the majority of the spreadsheets do not. When I merge the two and try to sort by say, city, the spreadsheets with the extra space don't sort right as you would expect. I tried using the trim function to take the spaces out of those sheets, but it doesn't work. Then I tried adding a space to all the normal spreadsheets using the =" "&A2 function. This worked, but when I merge these files back with the ones that came with the extra space, they still don't sort right. I will say that every time I open one of the spreadsheets that came with the extra space, I get the message "The file you are trying to open, 'order_862_prospects (4).xls, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?" I'm thinking that maybe I need to use a text to columns type functions, but really don't know how. I would love some help with this! Julie -- Julie |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
merging spreadsheets/one has extra space in each cell
If TRIM did not work, then maybe you have the non-breaking space
character (code 160) instead of a normal space (code 32) at the beginning of your data. You can get rid of it by highlighting the block of data then CTRL-H (or Edit|Replace): Find what: Alt-0160 Replace with: leave blank click Replace All For Alt-0160, hold down the Alt key and type 0160 on the numeric keypad (NOT on the numbers above QWETY etc). Hope this helps. Pete On Mar 25, 4:23*pm, Julie wrote: I have several spreadsheets that contain customer information, ie. name address, etc. *These spreadsheets have been sent to us by the manufacturer we represent. *One group of spreadsheets has a space before the text in every cell, while the majority of the spreadsheets do not. *When I merge the two and try to sort by say, city, the spreadsheets with the extra space don't sort right as you would expect. I tried using the trim function to take the spaces out of those sheets, but it doesn't work. *Then I tried adding a space to all the normal spreadsheets using the =" "&A2 function. *This worked, but when I merge these files back with the ones that came with the extra space, they still don't sort right. I will say that every time I open one of the spreadsheets that came with the extra space, I get the message "The file you are trying to open, 'order_862_prospects (4).xls, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?" I'm thinking that maybe I need to use a text to columns type functions, but really don't know how. I would love some help with this! Julie -- Julie |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
merging spreadsheets/one has extra space in each cell
You're a genius !!! It was easy and worked perfectly.
Thank-you so much - really -- Julie "Pete_UK" wrote: If TRIM did not work, then maybe you have the non-breaking space character (code 160) instead of a normal space (code 32) at the beginning of your data. You can get rid of it by highlighting the block of data then CTRL-H (or Edit|Replace): Find what: Alt-0160 Replace with: leave blank click Replace All For Alt-0160, hold down the Alt key and type 0160 on the numeric keypad (NOT on the numbers above QWETY etc). Hope this helps. Pete On Mar 25, 4:23 pm, Julie wrote: I have several spreadsheets that contain customer information, ie. name address, etc. These spreadsheets have been sent to us by the manufacturer we represent. One group of spreadsheets has a space before the text in every cell, while the majority of the spreadsheets do not. When I merge the two and try to sort by say, city, the spreadsheets with the extra space don't sort right as you would expect. I tried using the trim function to take the spaces out of those sheets, but it doesn't work. Then I tried adding a space to all the normal spreadsheets using the =" "&A2 function. This worked, but when I merge these files back with the ones that came with the extra space, they still don't sort right. I will say that every time I open one of the spreadsheets that came with the extra space, I get the message "The file you are trying to open, 'order_862_prospects (4).xls, is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?" I'm thinking that maybe I need to use a text to columns type functions, but really don't know how. I would love some help with this! Julie -- Julie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extra Space With Buttons | Excel Discussion (Misc queries) | |||
add extra space before comma | Excel Worksheet Functions | |||
How do I delete an extra space in every cell in a column? | Excel Discussion (Misc queries) | |||
Extra Line Space | Excel Discussion (Misc queries) | |||
Extra line space | Excel Discussion (Misc queries) |