Solved open office space issues

January 16, 2014 at 09:41:20
Specs: Windows XP
Good afternoon when I am exporting files from our data base a space is appearing before on item in the coloum on open office excel can you please advise how to remove this without doing them manually any help will be much appreciated

See More: open office space issues

Report •

#1
January 16, 2014 at 09:57:48
What is "open office excel"? Do you mean Open Office Calc?

Assuming Calc, the TRIM function should remove the space, as described here:

https://wiki.openoffice.org/wiki/Do...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 16, 2014 at 10:05:32
Sorry i meant calc,

Sorry to sound daft but could you advise where the trim option is as I cant seam to locate it

Thanks

Rob


Report •

#3
January 16, 2014 at 10:24:22
✔ Best Answer
Did you look at the link I posted? It was posted for a reason.

TRIM is not an "option", it's a function, just like SUM, IF, etc.

Let's say you have " My_Name" in A1 - ignore the quotes, but note the leading space.

If you enter =TRIM(A1) in B1, you should get "My_Name" in B1 - again, ignore the quotes but note that the leading space is gone.

Once you've removed the space with the TRIM function, you can Copy the results of the formula and do a PasteSpecial to paste the results of a formula, not the formula itself, over the original data.

I should add that the character that you are referring to as a "space" might not be a "space". It might be some type of hidden/non-printable character. Copying data from a database can be tricky.

If TRIM doesn't work, you might want to try the CLEAN function, as described here:

https://wiki.openoffice.org/wiki/Do...

I've even seen cases in Excel where both functions had to be used in conjunction in order to get rid of all the extra characters:

=CLEAN(TRIM(A1))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 16, 2014 at 10:36:36
Thank you I have managed to do one cell at a time can this be done for mutable cells at a time please.

Thanks again for all your help


Report •

#5
January 16, 2014 at 11:01:18
Enter the formula in e.g. B1 and drag it down. It will update row by row as you drag it down.

You can then select the entire range for the Copy...PasteSpecial operation.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
January 16, 2014 at 11:12:47
Thank you for all your assistance this has been very helpful

Report •

Ask Question