Add a single quote to a cell in excel

Forums › Desktop computing › Add a single quote to a cell in excel

AncestralGeek

91 posts

Master Geek

Lifetime subscriber

#61487 17-May-2010 20:08

Is there another way to add a single quote to the contents of a cell other than by directly editing the cell or using the following formula ="'"&B2 where the value of B2 is 123456. The result of the formula is different from directly editing the cell in some way as the .xls (Excel 95 format) file can not be used as a source file for importing. Have used paste special to remove the formula but it make no difference.

So much to learn, so little time.

alikat

405 posts

Ultimate Geek
+1 received by user: 40

Trusted

#331047 17-May-2010 20:29

Not sure what you're trying to achieve there, but if you're wanting to quote the contents of that cell then why not just =B2

Or paste special 'values' ?

DS248

1702 posts

Uber Geek
+1 received by user: 771

Lifetime subscriber

#331081 17-May-2010 21:27

If you simply want to convert a numerical value to text, use:

=TEXT(B2,"@")

Or replace "@" by your preferred format - look up TEXT function in Help or Google.

rvangelder

352 posts

Ultimate Geek

#331089 17-May-2010 21:41

AncestralGeek: Is there another way to add a single quote to the contents of a cell other than by directly editing the cell or using the following formula ="'"&B2 where the value of B2 is 123456. The result of the formula is different from directly editing the cell in some way as the .xls (Excel 95 format) file can not be used as a source file for importing. Have used paste special to remove the formula but it make no difference.

If you edit a cell, and start the content with a single quote, then it tells Excel that what follows the single quote should be treated as text.
For example, when typing a phone number, you could type
'091234567
and Excel would treat it as text instead of 91 million.

You could type two single quotes instead.
eg ''123456

AncestralGeek

91 posts

Master Geek

Lifetime subscriber

#332010 19-May-2010 22:53

Thanks people for the answers. The excel file is used to transfer data between two legacy applications. The first column in the spreadsheet is titled Invoice Number and when the file is created by exporting data the values are numeric. Unfortunately the application that imports the file expects the value to be Text. It is only when the single quote is present in the cell that the import will work.

The information in rvaneldel reply gave me an idea and a bit of VBA code later I now no longer have to edit each cell. Just why the methods suggested in the other reply's, plus my own attempts, do not work I have no idea.

Anyway thanks again for your help.

So much to learn, so little time.

rvangelder

352 posts

Ultimate Geek

#332087 20-May-2010 09:14

You should format text columns as text.
That is, highlight your cells, then from the Format menu select Cells..., then from the Number tab select Text