I disagree. I am NOT going to type a leading quote into every sodding cell, just to allow it to display the leading zeros on an entire column of phone numbers. I have to format the column as text. What is so effing difficult for MS to allow Excel to show leading zeroes? My spreadsheets are often garnered from assorted sources of data, so I cannot control how it arrives. On this note, why does Excel sometimes decide, when copying data from one text formatted cell to another text formatted cell, to reset the formatting to number?
In my view, the number formatting dialogue should have a bit saying allow leading zeroes, yes/no.
They do?
Format-> Custom -> 0000 (or as many zeros as you want)
But doesn't that stick the relevant number of zeroes in front of everything? All I want it to do is leave my bloody zeroes alone, i.e. if I type one in, or one is there in data pasted in from elsewhere, just leave it as it is, don't mess about with it.
My spreadsheets are usually garnered from a raft of sources, sometimes extracts from our own housing system, which vomits out .csv files, sometimes Govt data which is usually .xls, and after they have been initially compiled, additional data is gathered from elsewhere, and either pasted in, or manually typed in. Quite often they may be project lists, of addresses, and when more people come on board, we add their details, and put their phone number in the relevant column which is already formatted as text, to stop the leading zero suppression, and sometimes, but not always, Excel goes "oh look, that looks like a number you are putting in there, I'll re-format it as one, and wipe off the leading zero." Just fucking stoppit, excel!
I'll explore the "get external data" dialogue, but often its a case of having a little snippet of data from somewhere, possibly even an email, and dropping it in, and that isn't really appropriate.
Next rant isn't aimed at Excel, its the idiots that wrote our housing system, whoever thought that stick the house number and street all in one cell was clever? How the fuck am I supposed to sort that? Cue much jibbling with adding spaces by replacing single ones with triple ones, then doing a text to columns, then removing the spaces. the space jibbling is because I have to have fixed delimiters because I can't use spaces as delimiters because it would separate "high" and "Street" inot separate cells, and of course house numbers can be from a single digit up to 4 digits, including an a or b on the end. Ah, of course its a pointless rant, because the system is by Crapita....
