How do you remove trailing spaces withour Trim function?

R

RajenRajput1

Hi.

How do you remove trailing spaces?


I have copied over some statement data from my online banking with HSBC.

Column A has all the debits. Column B has all the credits. Column C has
the current balance.

Column A somehow has values stored as text and the values have trailing
spaces. For example, instead of "34.22" there is "34.22 " inserted.

This means that I cannot make any calculations on the numbers in column A.

I have tried using the Trim function to remove the trailing space, but even
after a paste values, the space is still there.

Does anyone have any other ideas?

The method I used was to put in column D, =trim(a1). Then in column E I
pasted values from column D. I noticed that the spaces were still there,
which means converting to a number did not work.

Any help would be appreciated as I have many many rows of data.

Thank you.
 
N

NDBC

one rough and ready way to convert numbers stored as text into numbers is put
the number 1 in a blank cell somewhere on the worksheet. copy the number one
then select all the numbers which are saved as text and use paste special and
then choose the function check box multiply by. All the numbers will be
converted and then can be used to do calculations.

Hope this helps.
 
P

Per Jessen

Hi

Enter 1 in an unused cell, then copy the cell. Select A1 and goto Edit
PasteSpecial > In Operation section, check "Multiply" > OK

Hopes this helps.
....
Per
 
R

RajenRajput1

Thank you for your help so far.

I tried these two methods, but they do not work. Reason being, is because
of the trailing space.
 
N

NDBC

my only other suggestion would be to select one column of data go to

Data >text to columns then choose delimited then choose space as the
delimiter and see if that takes it out.
 
R

RajenRajput1

Thank you for your help.

The text to columns worked on Office 2007 but not on 2003.

Thank you very much.
 
F

Fred Smith

Your problem likely is that you don't have a space in your cell, you have a
non-printing character. Look up "Removing spaces and non-printing characters
from text" in Help.

A common problem in web-based data is non-breaking spaces (char(160)). If
this is your problem, you can get rid of it with:
=TRIM(SUBSTITUTE(A4,CHAR(160),CHAR(32)))

Regards,
Fred.
 
R

RagDyeR

You *can* make it (TTC) work in earlier versions, but you have to manually
add the Char(160) character.

Select the data,
In first step of the TTC wizard click "Delimited", <Next>,
Then put a check in <Space> (just to be sure),
*AND ALSO* click in "Other",
Then type in the Char(160) character by:
holding down <Alt>,
and typing
0160
using the keys from the num keypad, *not* the numbers under the function
keys!

Then <Finish>

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


Thank you for your help.

The text to columns worked on Office 2007 but not on 2003.

Thank you very much.
 
R

RajenRajput1

Another way I thought of was,

=left(a1,len(a1)-1)

Thanks for all of your help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top