excel question

1337_KR3W · 1 Nov 2006 at 10:27

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 · 1 Nov 2006 at 11:41

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

PeteLucky7 · 1 Nov 2006 at 11:53

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

should be sorted in 2 secs doing that

JonC · 1 Nov 2006 at 20:46

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 · 1 Nov 2006 at 20:48

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

Excel said:
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 · 1 Nov 2006 at 22:28

Raist said:
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 · 2 Nov 2006 at 11:00

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 · 2 Nov 2006 at 20:04

1337_KR3W said:
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....