Tjyylli

2001: A Space Odyssey's use of the song "Daisy Bell" (Bicycle Built for Two); life imitates art or vice-versa?

Why am I getting the error "non-boolean type specified in a context where a condition is expected" for this request?

English words in a non-english sci-fi novel

What does an IRS interview request entail when called in to verify expenses for a sole proprietor small business?

How do pianists reach extremely loud dynamics?

Do I really need recursive chmod to restrict access to a folder?

Seeking colloquialism for “just because”

Why did the IBM 650 use bi-quinary?

Is the Standard Deduction better than Itemized when both are the same amount?

How to answer "Have you ever been terminated?"

What causes the vertical darker bands in my photo?

Why light coming from distant stars is not discrete?

porting install scripts : can rpm replace apt?

Is it true that "carbohydrates are of no use for the basal metabolic need"?

How does debian/ubuntu knows a package has a updated version

What's the purpose of writing one's academic biography in the third person?

Error "illegal generic type for instanceof" when using local classes

What does this icon in iOS Stardew Valley mean?

ListPlot join points by nearest neighbor rather than order

Why are there no cargo aircraft with "flying wing" design?

51k Euros annually for a family of 4 in Berlin: Is it enough?

Sci-Fi book where patients in a coma ward all live in a subconscious world linked together

The logistics of corpse disposal

What does F' and F" mean?

How to disable Excel number autoformat

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}

11

1

This problem is simple to correct but is very very annoying

what I'm doing is to type a hour range, eg. (0-1),(1-2),(2-3) so on
per cell row

DATE --- HOUR

1/1/2013 0-1
1/1/2013 1-2
1/1/2013 2-3
1/1/2013 3-4

Excel stupidly keeps formatting 1-2 to a date 2-Jan

to make matters worse is when I change it to a number format

it gives 41276

I also cannot find the AutoFormat option in excel 2010, I am aware of the Format button in home but there is no autoformat there.

microsoft-excel formatting microsoft-excel-2010

share|improve this question

asked Feb 14 '13 at 6:02

forumsforums

158116

migrated from stackoverflow.com Feb 14 '13 at 10:02

This question came from our site for professional and enthusiast programmers.

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Select cells, right-click on them and change cell format from General to Text
  
  – Mikhail Vladimirov
  Feb 14 '13 at 6:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hour range means 1AM -2AM?
  
  – user187182
  Feb 14 '13 at 6:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  tried changing it to text but it still keeps on insisting the date. And also using military format 1-24 so no am/pm there
  
  – forums
  Feb 14 '13 at 6:07
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  You have to change it to text before you enter values. See my answer.
  
  – David Zemens
  Feb 14 '13 at 6:09
  

  
  
  
  


• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  Simplest way is to type '1-2 - the ' tells Excel to treat the entry as text
  
  – chris neilsen
  Feb 14 '13 at 7:07
  

  
  
  
  

add a comment | 

11

1

This problem is simple to correct but is very very annoying

what I'm doing is to type a hour range, eg. (0-1),(1-2),(2-3) so on
per cell row

DATE --- HOUR

1/1/2013 0-1
1/1/2013 1-2
1/1/2013 2-3
1/1/2013 3-4

Excel stupidly keeps formatting 1-2 to a date 2-Jan

to make matters worse is when I change it to a number format

it gives 41276

I also cannot find the AutoFormat option in excel 2010, I am aware of the Format button in home but there is no autoformat there.

microsoft-excel formatting microsoft-excel-2010

share|improve this question

asked Feb 14 '13 at 6:02

forumsforums

158116

migrated from stackoverflow.com Feb 14 '13 at 10:02

This question came from our site for professional and enthusiast programmers.

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Select cells, right-click on them and change cell format from General to Text
  
  – Mikhail Vladimirov
  Feb 14 '13 at 6:05
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Hour range means 1AM -2AM?
  
  – user187182
  Feb 14 '13 at 6:06
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  tried changing it to text but it still keeps on insisting the date. And also using military format 1-24 so no am/pm there
  
  – forums
  Feb 14 '13 at 6:07
  

  
  
  
  


• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  You have to change it to text before you enter values. See my answer.
  
  – David Zemens
  Feb 14 '13 at 6:09
  

  
  
  
  


• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  Simplest way is to type '1-2 - the ' tells Excel to treat the entry as text
  
  – chris neilsen
  Feb 14 '13 at 7:07
  

  
  
  
  

add a comment | 

This problem is simple to correct but is very very annoying

what I'm doing is to type a hour range, eg. (0-1),(1-2),(2-3) so on
per cell row

DATE --- HOUR

1/1/2013 0-1
1/1/2013 1-2
1/1/2013 2-3
1/1/2013 3-4

Excel stupidly keeps formatting 1-2 to a date 2-Jan

to make matters worse is when I change it to a number format

it gives 41276

I also cannot find the AutoFormat option in excel 2010, I am aware of the Format button in home but there is no autoformat there.

microsoft-excel formatting microsoft-excel-2010

share|improve this question

asked Feb 14 '13 at 6:02

forumsforums

158116

share|improve this question

asked Feb 14 '13 at 6:02

forumsforums

158116

share|improve this question

asked Feb 14 '13 at 6:02

forumsforums

158116

asked Feb 14 '13 at 6:02

forumsforums

158116

asked Feb 14 '13 at 6:02

forumsforums

158116

4 Answers
4

active

oldest

votes

3

Before you enter any values in the column/cells, change the cell format to Text.

Or, put this in the Workbook code module to force this formatting on all sheets, each time the workbook is opened.

Private Sub Workbook_Open()

Dim sh As Worksheet



For Each sh In Me.Sheets

    sh.Cells.NumberFormat = "@"

Next



End Sub

share|improve this answer

edited Oct 9 '17 at 13:34

phuclv

10.7k64297

answered Feb 14 '13 at 6:08

David ZemensDavid Zemens

54649

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I think using the custom format @ solved it for me, the Text doesn't seem to work w/ch is weird. Also do you know of a way to disable/enable autoformatting function of excel? There used to be one in xl2003
  
  – forums
  Feb 14 '13 at 6:14
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @forums "custom format @" - it's a symbol for Text format in VBA))
  
  – Peter L.
  Feb 14 '13 at 8:09
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I tried googling and knew about changing it to text but like my comment above, it keeps being reverted to date. Don't know why @ worked fine. Thanks for the ms forum link too, learned that autoformatting cannot be simply turned off :sigh:
  
  – forums
  Feb 14 '13 at 14:01
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I feel your pain with excel. My boss just wanted to do everything in excel when I was insisting to use access :sigh again:. Also do you think the reason it reverts to date is because the adjacent cell is in date format?
  
  – forums
  Feb 14 '13 at 14:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  can't say for sure about the revert issue, but Excel (and other MS products, for that matter) have been known to be a bit user-unfriendly when trying to "intelligently" do things, like formatting (Excel), indenting (Word), etc.
  
  – David Zemens
  Feb 14 '13 at 14:22
  

  
  
  
  

 | 
show 2 more comments

4

1. cut and paste everything into note_pad


2. delete what ever left in xl


3. Select all needed cells, columns row to text format


4. re-paste from note_pad


5. if you need later you can change to date, time formats

share|improve this answer

edited Jul 29 '14 at 6:31

Jan Doggen

3,18552743

answered Jul 29 '14 at 4:21

user351036user351036

411

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Not happy about having to take the extra step of copy and pasting into notepad then re-pasting into excel. It's the only way my "dashed" entries don't convert to a date. The dashed entries are coming from a java table from an outside source. No other work around has stopped the conversion other than going through the notepad step.
  
  – CSmith
  Jul 30 '17 at 20:28
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Another way I use is to put ="1-2" as I have needed the same range info for stuff before - downside is it does not work in calculations...
  
  – Solar Mike
  Jul 30 '17 at 20:39
  

  
  
  
  

add a comment | 

0

1. Select all your '.csv' data and copy to notepad


2. Set all cells in new worksheet in Excel to text


3. Paste all your data from Notepad as text


4. Copy the "affected" column back to Notepad


5. Change all your . to , (e.g. 10.00 to 10,00)


6. Open new Excel book - IMPORTANT


7. Copy your column from Notepad to this new Excel book


8. Copy and paste the column back to your original Excel book

The only drawback is that you have your numbers now with (,) instead of (.).

share|improve this answer

edited Oct 9 '17 at 13:51

answered Oct 9 '17 at 12:41

RobertRobert

11

add a comment | 

0

Another way is:

1. save sheet as CSV


2. rename it to .txt
   


3. reopen it in Excel


4. import module is invoked


5. switch from general to text format for each numeric column

share|improve this answer

edited 17 hours ago

G-Man

5,934112462

answered 18 hours ago

tired of exceltired of excel

1

New contributor

tired of excel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f552042%2fhow-to-disable-excel-number-autoformat%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

3

Before you enter any values in the column/cells, change the cell format to Text.

Or, put this in the Workbook code module to force this formatting on all sheets, each time the workbook is opened.

Private Sub Workbook_Open()

Dim sh As Worksheet



For Each sh In Me.Sheets

    sh.Cells.NumberFormat = "@"

Next



End Sub

share|improve this answer

edited Oct 9 '17 at 13:34

phuclv

10.7k64297

answered Feb 14 '13 at 6:08

David ZemensDavid Zemens

54649

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I think using the custom format @ solved it for me, the Text doesn't seem to work w/ch is weird. Also do you know of a way to disable/enable autoformatting function of excel? There used to be one in xl2003
  
  – forums
  Feb 14 '13 at 6:14
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @forums "custom format @" - it's a symbol for Text format in VBA))
  
  – Peter L.
  Feb 14 '13 at 8:09
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I tried googling and knew about changing it to text but like my comment above, it keeps being reverted to date. Don't know why @ worked fine. Thanks for the ms forum link too, learned that autoformatting cannot be simply turned off :sigh:
  
  – forums
  Feb 14 '13 at 14:01
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I feel your pain with excel. My boss just wanted to do everything in excel when I was insisting to use access :sigh again:. Also do you think the reason it reverts to date is because the adjacent cell is in date format?
  
  – forums
  Feb 14 '13 at 14:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  can't say for sure about the revert issue, but Excel (and other MS products, for that matter) have been known to be a bit user-unfriendly when trying to "intelligently" do things, like formatting (Excel), indenting (Word), etc.
  
  – David Zemens
  Feb 14 '13 at 14:22
  

  
  
  
  

 | 
show 2 more comments

3

Before you enter any values in the column/cells, change the cell format to Text.

Or, put this in the Workbook code module to force this formatting on all sheets, each time the workbook is opened.

Private Sub Workbook_Open()

Dim sh As Worksheet



For Each sh In Me.Sheets

    sh.Cells.NumberFormat = "@"

Next



End Sub

share|improve this answer

edited Oct 9 '17 at 13:34

phuclv

10.7k64297

answered Feb 14 '13 at 6:08

David ZemensDavid Zemens

54649

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I think using the custom format @ solved it for me, the Text doesn't seem to work w/ch is weird. Also do you know of a way to disable/enable autoformatting function of excel? There used to be one in xl2003
  
  – forums
  Feb 14 '13 at 6:14
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @forums "custom format @" - it's a symbol for Text format in VBA))
  
  – Peter L.
  Feb 14 '13 at 8:09
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I tried googling and knew about changing it to text but like my comment above, it keeps being reverted to date. Don't know why @ worked fine. Thanks for the ms forum link too, learned that autoformatting cannot be simply turned off :sigh:
  
  – forums
  Feb 14 '13 at 14:01
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I feel your pain with excel. My boss just wanted to do everything in excel when I was insisting to use access :sigh again:. Also do you think the reason it reverts to date is because the adjacent cell is in date format?
  
  – forums
  Feb 14 '13 at 14:10
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  can't say for sure about the revert issue, but Excel (and other MS products, for that matter) have been known to be a bit user-unfriendly when trying to "intelligently" do things, like formatting (Excel), indenting (Word), etc.
  
  – David Zemens
  Feb 14 '13 at 14:22
  

  
  
  
  

 | 
show 2 more comments

Before you enter any values in the column/cells, change the cell format to Text.

Or, put this in the Workbook code module to force this formatting on all sheets, each time the workbook is opened.

Private Sub Workbook_Open()

Dim sh As Worksheet



For Each sh In Me.Sheets

    sh.Cells.NumberFormat = "@"

Next



End Sub

share|improve this answer

edited Oct 9 '17 at 13:34

phuclv

10.7k64297

answered Feb 14 '13 at 6:08

David ZemensDavid Zemens

54649

Private Sub Workbook_Open()

Dim sh As Worksheet



For Each sh In Me.Sheets

    sh.Cells.NumberFormat = "@"

Next



End Sub

share|improve this answer

edited Oct 9 '17 at 13:34

phuclv

10.7k64297

answered Feb 14 '13 at 6:08

David ZemensDavid Zemens

54649

edited Oct 9 '17 at 13:34

phuclv

10.7k64297

edited Oct 9 '17 at 13:34

phuclv

10.7k64297

answered Feb 14 '13 at 6:08

David ZemensDavid Zemens

54649

answered Feb 14 '13 at 6:08

David ZemensDavid Zemens

54649

4

1. cut and paste everything into note_pad


2. delete what ever left in xl


3. Select all needed cells, columns row to text format


4. re-paste from note_pad


5. if you need later you can change to date, time formats

share|improve this answer

edited Jul 29 '14 at 6:31

Jan Doggen

3,18552743

answered Jul 29 '14 at 4:21

user351036user351036

411

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Not happy about having to take the extra step of copy and pasting into notepad then re-pasting into excel. It's the only way my "dashed" entries don't convert to a date. The dashed entries are coming from a java table from an outside source. No other work around has stopped the conversion other than going through the notepad step.
  
  – CSmith
  Jul 30 '17 at 20:28
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Another way I use is to put ="1-2" as I have needed the same range info for stuff before - downside is it does not work in calculations...
  
  – Solar Mike
  Jul 30 '17 at 20:39
  

  
  
  
  

add a comment | 

4

1. cut and paste everything into note_pad


2. delete what ever left in xl


3. Select all needed cells, columns row to text format


4. re-paste from note_pad


5. if you need later you can change to date, time formats

share|improve this answer

edited Jul 29 '14 at 6:31

Jan Doggen

3,18552743

answered Jul 29 '14 at 4:21

user351036user351036

411

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Not happy about having to take the extra step of copy and pasting into notepad then re-pasting into excel. It's the only way my "dashed" entries don't convert to a date. The dashed entries are coming from a java table from an outside source. No other work around has stopped the conversion other than going through the notepad step.
  
  – CSmith
  Jul 30 '17 at 20:28
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Another way I use is to put ="1-2" as I have needed the same range info for stuff before - downside is it does not work in calculations...
  
  – Solar Mike
  Jul 30 '17 at 20:39
  

  
  
  
  

add a comment | 

1. cut and paste everything into note_pad


2. delete what ever left in xl


3. Select all needed cells, columns row to text format


4. re-paste from note_pad


5. if you need later you can change to date, time formats

share|improve this answer

edited Jul 29 '14 at 6:31

Jan Doggen

3,18552743

answered Jul 29 '14 at 4:21

user351036user351036

411

share|improve this answer

edited Jul 29 '14 at 6:31

Jan Doggen

3,18552743

answered Jul 29 '14 at 4:21

user351036user351036

411

edited Jul 29 '14 at 6:31

Jan Doggen

3,18552743

edited Jul 29 '14 at 6:31

Jan Doggen

3,18552743

answered Jul 29 '14 at 4:21

user351036user351036

411

answered Jul 29 '14 at 4:21

user351036user351036

411

0

1. Select all your '.csv' data and copy to notepad


2. Set all cells in new worksheet in Excel to text


3. Paste all your data from Notepad as text


4. Copy the "affected" column back to Notepad


5. Change all your . to , (e.g. 10.00 to 10,00)


6. Open new Excel book - IMPORTANT


7. Copy your column from Notepad to this new Excel book


8. Copy and paste the column back to your original Excel book

The only drawback is that you have your numbers now with (,) instead of (.).

share|improve this answer

edited Oct 9 '17 at 13:51

answered Oct 9 '17 at 12:41

RobertRobert

11

add a comment | 

0

1. Select all your '.csv' data and copy to notepad


2. Set all cells in new worksheet in Excel to text


3. Paste all your data from Notepad as text


4. Copy the "affected" column back to Notepad


5. Change all your . to , (e.g. 10.00 to 10,00)


6. Open new Excel book - IMPORTANT


7. Copy your column from Notepad to this new Excel book


8. Copy and paste the column back to your original Excel book

The only drawback is that you have your numbers now with (,) instead of (.).

share|improve this answer

edited Oct 9 '17 at 13:51

answered Oct 9 '17 at 12:41

RobertRobert

11

add a comment | 

1. Select all your '.csv' data and copy to notepad


2. Set all cells in new worksheet in Excel to text


3. Paste all your data from Notepad as text


4. Copy the "affected" column back to Notepad


5. Change all your . to , (e.g. 10.00 to 10,00)


6. Open new Excel book - IMPORTANT


7. Copy your column from Notepad to this new Excel book


8. Copy and paste the column back to your original Excel book

The only drawback is that you have your numbers now with (,) instead of (.).

share|improve this answer

edited Oct 9 '17 at 13:51

answered Oct 9 '17 at 12:41

RobertRobert

11

share|improve this answer

edited Oct 9 '17 at 13:51

answered Oct 9 '17 at 12:41

RobertRobert

11

answered Oct 9 '17 at 12:41

RobertRobert

11

answered Oct 9 '17 at 12:41

RobertRobert

11

0

Another way is:

1. save sheet as CSV


2. rename it to .txt
   


3. reopen it in Excel


4. import module is invoked


5. switch from general to text format for each numeric column

share|improve this answer

edited 17 hours ago

G-Man

5,934112462

answered 18 hours ago

tired of exceltired of excel

1

New contributor

tired of excel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

Another way is:

1. save sheet as CSV


2. rename it to .txt
   


3. reopen it in Excel


4. import module is invoked


5. switch from general to text format for each numeric column

share|improve this answer

edited 17 hours ago

G-Man

5,934112462

answered 18 hours ago

tired of exceltired of excel

1

New contributor

tired of excel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

Another way is:

1. save sheet as CSV


2. rename it to .txt
   


3. reopen it in Excel


4. import module is invoked


5. switch from general to text format for each numeric column

share|improve this answer

edited 17 hours ago

G-Man

5,934112462

answered 18 hours ago

tired of exceltired of excel

1

New contributor

tired of excel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this answer

edited 17 hours ago

G-Man

5,934112462

answered 18 hours ago

tired of exceltired of excel

1

New contributor

tired of excel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

edited 17 hours ago

G-Man

5,934112462

edited 17 hours ago

G-Man

5,934112462

answered 18 hours ago

tired of exceltired of excel

1

New contributor

tired of excel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

answered 18 hours ago

tired of exceltired of excel

1