Another annoying Excel thing

Gers

After a big annoying problem with Excel earlier this month I have decided to tackle a rather smaller but still annoying thing,

On my banking workbooks I have a number of accounts and sort codes which start with either a single or double 0. Each time I copy my lists (weekly and monthly) the numbers which I've saved as text then gets the wee triangle error message attached so I go to round each one to 'ignore error'.

Is there a way of fixing the numbers with the 0 or 00 start please?

Rodders53

I just ignore the wee triangle (it is an informative marker that the number in the cell is formatted as 'text' or preceded by an apostrophe ' in the version of Excel I use).

It appears to be doing the same for you, and listing some options you might want to do...  but you don't !

tizerbelle

In Excel go to File > Options > Data then under Error Checking Rules untick the Numbers formatted as text or preceded by an apostrophe option. Then click OK

Gers

tizerbelle said:

In Excel go to File > Options > Data then under Error Checking Rules untick the Numbers formatted as text or preceded by an apostrophe option. Then click OK

Thanks for this  - I couldn't find it under Data but under Formulas, unchecked the box and all is perfect now.

Polmop

Put a ' before the number. 

DullGreyGuy

Gers said:

After a big annoying problem with Excel earlier this month I have decided to tackle a rather smaller but still annoying thing,

On my banking workbooks I have a number of accounts and sort codes which start with either a single or double 0. Each time I copy my lists (weekly and monthly) the numbers which I've saved as text then gets the wee triangle error message attached so I go to round each one to 'ignore error'.

Is there a way of fixing the numbers with the 0 or 00 start please?

It really depends as to where the data is coming from and what you want to do with it. 

I'm a big fan of custom formatting in Excel and so if you had an account number of 00123456 I'd enter it in excel as either that (if copy/pasting) or 123456 (if typing). You can then set the formatting for that cell/column to be '00000000' and so it would automatically put any lead zeros you need in to get it to an 8 digit number again. Similarly for the SortCode field I format as "00-00-00" so again lead zeros are there if required. 

Less of an issue for this use case as you are unlikely to ever be doing number based queries/calculations on these but have had plenty of other cases where retaining it as a number was necessary but fixed length with lead zeros was required.