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

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

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