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.

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.

"RajenRajput1" wrote:

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.

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

On 27 Jul., 12:41, RajenRajput1
wrote:
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.

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.



"Per Jessen" wrote:

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

On 27 Jul., 12:41, RajenRajput1
wrote:
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.

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" wrote:

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.

Thank you for your help.

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

Thank you very much.



"NDBC" wrote:

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" wrote:

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.

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.

"RajenRajput1" wrote in message
...
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.

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!
================================================== ===


"RajenRajput1" wrote in message
...
Thank you for your help.

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

Thank you very much.



"NDBC" wrote:

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" wrote:

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.

Another way I thought of was,

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

Thanks for all of your help.


"NDBC" wrote:

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" wrote:

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.