Add apostrophe to beginning of long list of numbers?

[Bret1]

I have a long column of numbers ranging from about 10 to 1000. I need to apply an apostrophe to the beginning of each one (to make them text) . Is there a simple way to do this using the Find/Replace? Or other method? Thanks.

 

[sandy666]

maybe easier will be change format of column to text

 

[Rick Rothstein]

I agree with Sandy as to the method to use; however, I am wondering why you think you need to do this in the first place?

 

[shg]

Format the column as Text (which will left-justify the numbers but not convert them to text), then Data > Text to Columns > Finish.

 

[Bret1]

I agree with Sandy as to the method to use; however, I am wondering why you think you need to do this in the first place?

I have a a lot of numerical data I'm charting, with a column of "reference" numbers down the middle (I know the reference numbers are usually on the side of the data, but the reference numbers changed, so now I basically have 2 columns of reference numbers). I need to make all the "reference" numbers text, so they won't be included in the charts.

 

[Bret1]

Thanks for the idea of just making the whole column "text" format, but unfortunately, that didn't work. I will need to physically add the apostrophe to the beginning of each number down the column. I'm hoping there is a way I can Find/Replace only (all) numbers with "1XX" (3 digits total) and replace with "'1XX" (same 3 digits with the apostrophe at beginning). Or some way to select all in the column, and just add an apostrophe to the beginning of each cell..... Maybe a Macro?

 

[Rick Rothstein]

Format the column as Text (which will left-justify the numbers but not convert them to text), then Data > Text to Columns > Finish.

Good point, but I had to proceed to the 3rd dialog page for Text To Column and deliberately specify the column as Text before the ISNUMBER function would report FALSE for the cells.

 

[sandy666]

if numbers are in column A then in column B ="'"&A1
copy column B then paste values in column A and delete column B

or as Rick mentioned

so in this case =ISTEXT(O3) = TRUE

 

[Rick Rothstein]

Thanks for the idea of just making the whole column "text" format, but unfortunately, that didn't work.

TextTo Columns works... see what I posted in Message #7 for how to do it.

 

[Bret1]

Good point, but I had to proceed to the 3rd dialog page for Text To Column and deliberately specify the column as Text before the ISNUMBER function would report FALSE for the cells.

I'm sorry, but I don't even understand this... Could you break it down some more?