Trimming spaces from cells containing numbers

[Genio]

This is probably super simple, but I cannot seem to make it work. I used the TRIM(cell) function, but it is not working. If I have a numerical value in the cell does it make a difference?

What I am doing wrong here?

 

[kskinne]

Hi Genio

need a little information on what exactly you are trying to do - give us a little background and we'll try to help you out

thanks,
kevin

 

[Genio]

Sorry, I should have included more.

I am importing some numbers by copy/pasting from a web source. The numbers are in a spreadsheet format, but when you copy and past the whole line of cells (1 row, 5 columns), there are extra spaces in the cells. The values paste over to Excel but the spaces are in the left side of the value in the cell.

I am pasting these values over in, for example, cells A1 to E1 in a worksheet. I tried to put a formula in cell F1 based on the values I just pasted in but it would not work. Once I manually took out the spaces in the cells the formula in F1 worked. That made me think I needed the TRIM(cell) function but I could not get that to work.

I might just be using the formula wrong. I am not sure how to use the TRIM formula probably.

I hope that helps. I apologize for not providing a better description originally.

Thanks very much.

 

[RichardS]

Still not quite sure what you have, but I would suggest Excel is treating the numbers as text. Try entering a 0 in a blank cell, copy it, highlight all the target cells, and Edit, Paste Special, Add.

Does this work?

Richard

 

[Genio]

That worked for some of the cells. The ones it did not work for it gave me a "#VALUE" error.

I checked those cells and formatted them as numbers, but it did not change anything. They still had the "#VALUE" error.

The basic problem I'm having is I'm manually copying and pasting numbers from the web into cells in a spreadsheet and referencing those cells in a formula. The formula does not work unless I go into my pasted information and manually double click on the left side of the value in the cell and highlight/delete all spaces to the left of that value.

After doing that step my formula works. The formula is nothing complicated:

=(1-(H12*0.01))*(F12/I12)

F12, H12, and I12 are cells with information that I have pasted from the web. This formula lies in J12.

Does that make it clearer? If not, please let me know and I will expand it in any way you want.

Thanks again.

 

[RichardS]

Genio,

In some cells to the right of your pasted data, enter the formula

=TRIM(F12)

and copy down and across as needed. This will remove the spaces from the cells. As TRIM is a text function, yuor new cells will be text. You can then repeat the process we dicussed in the earlier post to convert them to numbers. Hopefully that will work for you.

Richard