15

I have a column of hexadecimal strings:

506078
363624
505974
505644

I need excel to store these as strings, not integers or floats.

  • Format -> Cells -> Custom. Option '0' – tmarthal Dec 11 '18 at 22:57
27

All you need to do for this one is put a single quote in front of your text. This also works for dates you don't want Excel picking up.

eg.

'23446

You can also format your entire column as text which will override the whole column. In Excel 2007, hilight your cells, Right click->Format Cells. Click on text from the options on the left.

| improve this answer | |
  • can I put a single quote for entire col? I have data that is about 60000 rows, I don't want to use any formula. – vaibhavcool20 Apr 7 '18 at 14:34
1

As answered above, formatting a number as string is not advisable because you need to activate the cell in order for the conversion to be actually executed (either by F2 or double clicking or use Find+Replace on the column, and replace a dot with a dot or any other character, but if you don't have a character persistent in each cell in your column then this workaround won't work). And once you enter a cell for some editing it will automatically return to number if the value is compatible.

Now, answering Scott's question in comments section. If you already have your data and manually adding a quote in front of your data in each cell is not an option you can use a helper column and write

="'"&A1

in B1, where A1 is the reference to your cell, and drag down across the formula in B1. At this point you will see the quote, but you can paste data in column B as values (CTRL+C & ALT+E+S and select values, or paste special as values from the top menu). Then find+replace a '(quote) with a '(quote) and you will have a column with values forced to text.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.