Excel/Openoffice Calc - Stripping spaces

[mh348]

Is there an easier way yo remove/strip spaces from an column in Excel/OpenOffice

I have Excel 07 on my home PC and OpenOffice on the other PC's and wold like to do it on both. If there's no built-it feature in Openoffice can it maybe be done using macos?

 

[K9JT]

Do you want to remove blank cells or,

remove the spaces in the cells?
eg, from "hello michael " to "hello michael"

 

[mh348]

Do you want to remove blank cells or,

remove the spaces in the cells?
eg, from "hello michael " to "hello michael"

remove space from within the cells.

 

[Jabberwocky]

ctrl+f
find and replace
in the find what box press spacebar
leave the replace with box empty
click on replace all

 

[K9JT]

I haven't had much experience with excel open office, but afaik, almost all the formula functions are the same. So if you are using the TRIM function in Excel 07, it should would just as well in Open office.

 

[K9JT]

ctrl+f
find and replace
in the find what box press spacebar
leave the replace with box empty
click on replace all

But that would also replace the spaces inbetween words as well?
eg, from "hello michael " to "hellomichael"

 

[mh348]

ctrl+f
find and replace
in the find what box press spacebar
leave the replace with box empty
click on replace all

Doesn always work, in openoffice, even though I just select a single column and from the find window choose column it removes the spaces from all the columns.

What I sometimes do is copy just the columt into notepad and use the find/replace which works but takes longer as I need to first copy from OO calc to notepad then back to OO Calc.

 

[mh348]

I haven't had much experience with excel open office, but afaik, almost all the formula functions are the same. So if you are using the TRIM function in Excel 07, it should would just as well in Open office.

I tried the TRIM function yesterday but it doesn't work correctly, for example if I have "082 123 456 7", it removes everything before the "7" so the cell is just left with the number "7" as it removes everything before the last space.

 

[mh348]

But that would also replace the spaces inbetween words as well?
eg, from "hello michael " to "hellomichael"

It's mainly for cell numbers as its for client contact details. I leave the columns with the names, other details as is.

 

[Jabberwocky]

heres another way
eg: =SUBSTITUTE(C17:C39," ","")

 

[Nod]

All the Calc functions: http://wiki.services.openoffice.org/wiki/Documentation/How_Tos/Calc:_Functions_listed_by_category

 

[qinglung]

Doesn always work, in openoffice, even though I just select a single column and from the find window choose column it removes the spaces from all the columns.

You have to choose the column and in the Find window, under More Options, also select "Current selection only". Then it should do the find and replace in the column that you chose and not the others as well.

 

[K9JT]

It's mainly for cell numbers as its for client contact details. I leave the columns with the names, other details as is.

Aah, ok, well, in that case, find and replace would have work fine, but as you said, you don't want it to find & replace the whole sheet, just one column.

Jabberwocky's suggestion would work well for that then.

heres another way
eg: =SUBSTITUTE(C17:C39," ","")