excel question

[1337_KR3W]

I have a spreadsheet, about 15000 of the entries inside of it need to be numbers stored as text. (they have a ' preceding the number).
I have tried concatenate, and an if statement, and while they give me the correct output, I still have to click to edit every cell and press enter to change the view of the number from eg. '6000 to 6000 with a green mark in the corner.

The numbers arent consecutive so I cant just change a few and drag down into all the other boxes.

Is there any easier way of getting this done than sitting here all day clicking in the little bar at the top and pressing enter?
The only way I can think of is to put it on a spare pc for a while and put a macro there to click and press enter (but this wont be any quicker than me doing the same).

 

[theMAD2]

Have you tried doing edit > replace, and replacing all 's with nothing?

 

[PeteLucky7]

Have you tried doing edit > replace, and replacing all 's with nothing?

should be sorted in 2 secs doing that

 

[JonC]

I had to do exactly the same thing today.

Do as above, a replace, but replace the 's with 's (so you have ' in both the find and replace fields). Click replace all and it should change all the cells to be "OMG it's a number as text" green corner cells.

 

[SourChipmunk]

Select the columns or cells, then Format | Cells | and select "Text".

Text format cells are treated as text, even when a number is in the cell. The cell is displayed exactly as entered.

It doesn't get much easier than that.

 

[JonC]

Select the columns or cells, then Format | Cells | and select "Text".

It doesn't get much easier than that.

Unfortunately that doesn't always work for certain uses of the cells. As that's exactly how I had them before my find/replace trickery.

 

[1337_KR3W]

brilliant, works perfectly, i ended up doing it all with a macro to click + press enter running 6000 times then shifting columb and doing again, no work for me but 3 hours of waiting.

 

[JonC]

brilliant, works perfectly, i ended up doing it all with a macro to click + press enter running 6000 times then shifting columb and doing again, no work for me but 3 hours of waiting.

Find replace would have been quicker.... about 1000 records in 5 seconds....