How do you remove trailing spaces withour Trim function?

[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.

 

[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.

 

[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

 

[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.

 

[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.

 

[RajenRajput1]

Thank you for your help.

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

Thank you very much.

 

[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.

 

[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.

 

[RajenRajput1]

Another way I thought of was,

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

Thanks for all of your help.