0

I have a single cell with the value: 

426,427,433,439,442

This isn't a number, rather a list of numbers. If I try to add another number to the list, for example, 679. Excel changes the cell to read:

679,426,427,433,439,000

If I select the cell and format it as "text", it changes to:

 4.26427E+14

I've tried various cell formatting options, but I can't seem to get Excel to treat these numbers like text.

pnuts
  • 58,317
  • 11
  • 87
  • 139
gardener
  • 59
  • 5
  • It's has to do with localization settings, but @Gary's student provided the best answer, I tested it myself – davejal Dec 16 '15 at 01:26
  • no, I copied the cells, changed formatting to text and then applied @Gary's student solution – davejal Dec 16 '15 at 01:31

3 Answers3

2

First place a single quote (apostrophe) in front of the set of numbers and add the latest value at the end appropriate position.

  1. add (apostrophe) in front of numbers
  2. add (comma's) between
  3. add any number by adding a (comma)
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • 1
    While I upvoted this answer, please try to add the steps required to achieve this, as it wasn't straight forward for me even thought I got it to work. – davejal Dec 16 '15 at 01:33
  • then he should add it after the (apostrophe) – davejal Dec 16 '15 at 01:50
2

Copy and paste the column into Notepad, format a new column in Excel as text. In Notepad select all, copy it back out from Notepad into the column in Excel that you formatted for text.

DrHouseofSQL
  • 550
  • 5
  • 16
  • 2
    this will work best. I've got a column with hundreds of these sets of numbers, some longer than others. Do you know _why_ this solution works? – gardener Dec 16 '15 at 02:47
  • Only explanation I can give is because you are then going from and to places that have been told to be text. I know that's not a good explanation. – DrHouseofSQL Dec 16 '15 at 03:01
1

Possibly: 

=LEFT(A1,3)&","&MID(A1,4,3)&","&MID(A1,7,3)&","&MID(A1,10,3)&","&MID(A1,13,3)

It seems you have a number 426427433439442 with the commas purely a presentational aspect of the formatting. I take it you want the commas and the only way now may be to insert them.

pnuts
  • 58,317
  • 11
  • 87
  • 139