Trimming spaces from cells containing numbers

Genio

New Member
Joined
Jul 19, 2002
Messages
15
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?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

kskinne

Well-known Member
Joined
Feb 17, 2002
Messages
1,267
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 19, 2002
Messages
15
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

Well-known Member
Joined
Feb 16, 2002
Messages
761
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

New Member
Joined
Jul 19, 2002
Messages
15
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

Well-known Member
Joined
Feb 16, 2002
Messages
761
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
 

Forum statistics

Threads
1,144,312
Messages
5,723,658
Members
422,509
Latest member
Njugunah

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top