Strange Excel behaviour

[AlexanderBB]

I want to type 135,136,137,138,134,139 into a cell on my worksheet
No matter what I try, Excel insists on changing it to 135136137138134000
I looked at Format Cells and Number was set to Number. I think it should be General
But if I make it General and enter the number again, it's changed back to Number.

Grrrr.. what's going on ? !! It's been ok for 28 rows and now this.

 

[Michael M]

Have you tried setting the format to Text

 

[AlexanderBB]

Thanks, text sorted it. But the previous 27 are "General'. Ah well..

Whats XL2BB ?

 

[AlexanderBB]

Actually text changes it to scientific notation,
How do I make it just display what I type ? Entering long numbers many times is a pain !

 

[Alex Blakenburg]

XL2BB is an Excel Add-In that lets you post samples of your data in the Forum so that whoever helps doesn't have to recreate your data and also that they can see a sample of your actual data to determine patterns, location of the data, what data types are involved. Michael has a link to more information in his signature block.

Is your value a string of values or it meant to be a single number ?
Assuming you want it to be a string of values format the column you are entering the data into as Text BEFORE you enter the data into the cell(s).

 

[AlexanderBB]

Got it, thanks Alex. The Before bit seems the trick. It's a string of numbers, comma delimited. Meant to be as typed in and not altered.
The things went nuts before, changing some numbers removing the commas and added some spaces before it.
I was just about to change to Access!
Does it matter where you enter your data e.g. fx bar or the cell, any difference to how Excel shows it?

 

[Michael M]

It all boils down to the cell format, no matter where you enter it

 

[felixstraube]

Another option is to add an apostrophe at the start, then excel take the characters that follow as text, no matter how the cell is formatted.

If you type '10/1 for example it will stay 10/1 instead of excel transforming it into a date.
'100,112,123 will stay 100,112,123 (assuming that you thousands separator is ",")

Speaking of thousands separator... if coma is your decimal separator you could use semicolon to separate your number in the string (or any other character different from the thousands separator character)

 

[AlexanderBB]

Thank you.