Excel ****es me off - automatic formatting
Discussion
Is there a way to set Excel/Sheets to ONLY work with TEXT formatting and prevent it 100% from ever converting anything to AUTOMATIC?
No matter how much I prime the sheet to be TEXT beforehand, and ensure there are more than 1000 rows to accommodate all the data, whenever I paste in hexadecimal data or anything numerical that it thinks is a number, it completely ignores my TEXT setting and converts anything it thinks should be a number to a number. In the case of hexadecimal data this completely destroys it and by going back and setting it back to text, it doesn't convert it back to the hexadecimal.
All I want to do is just paste in my f
king data EXACTLY AS IT IS without Excel interfering it and changing it to a load of utter fking garbage because some millennial at Microsoft has programmed it on the assumption that every Excel user only uses numbers and so it will convert everything that looks like a number to a number format whether you want it or not.
It's my biggest piss-boiler with Excel/Sheets and there is no other program that will easily let me split data in columns by specifying the separator character myself.
No matter how much I prime the sheet to be TEXT beforehand, and ensure there are more than 1000 rows to accommodate all the data, whenever I paste in hexadecimal data or anything numerical that it thinks is a number, it completely ignores my TEXT setting and converts anything it thinks should be a number to a number. In the case of hexadecimal data this completely destroys it and by going back and setting it back to text, it doesn't convert it back to the hexadecimal.
All I want to do is just paste in my f
king data EXACTLY AS IT IS without Excel interfering it and changing it to a load of utter fking garbage because some millennial at Microsoft has programmed it on the assumption that every Excel user only uses numbers and so it will convert everything that looks like a number to a number format whether you want it or not.It's my biggest piss-boiler with Excel/Sheets and there is no other program that will easily let me split data in columns by specifying the separator character myself.
Would something like https://www.moderncsv.com/ be better for your needs?
IT doesn't work. I need to split one column with : validator and split another column with a T validator. You can only do one. Also paste special for values only only works fine for the initial paste, but then when you split it into another column it ignores the formatting and sets it back to automatic, so all the hexadecimal gets messed up. It's unfit for purpose. There are pages and pages about this on google and stackoverflow, with people coming up with lengthy scripts to work around it, but none of it works and just creates other problems.
Doofus said:
Paste Special?
Off topic, but paste special is really annoying. Not sure if its a quirk of my system somehow, but when I paste special something into some cells and I'm looking for which is the correct formatting it will only give a couple of seconds to look before going ahead and pasting. Not being an excel guru I don't know all the icons by sight, so need to hover over them to get the names and it just overrides that! I'm trying to get data in a table on a html page saved and formatted correctly so that I can import it as csv into a sql database.
One of the columns needs splitting, as it is formatted like abcd:053977 . I need the abcd bit to be on its own in one column and the 053977 bit to be on its own in another column.
Then another column is a date and time format, but I don't need the time, only the date. It is format like 2023-03-01T16:07:37Z. I only want the 2023-03-01 bit.
As there isn't a download option for the table on the html page, I've been CTRL A the whole page, opening a new text document (notepad) and pasting it in there, removing the unneeded header and footer crap and saving it as txt. I've tried saving it as csv, but of course it doesn'r put any separation character between the columns, so I then have to create my own when using the import wizard, but it only lets you set separator charater and then the whole thing messes up by changing the format back to general. This is with Sheets by the way - where I actually need it.
Current workaround is to use Excel import wizard which seems to keep the formatting after shift clicking the column headers and setting them all to text from general. This gives me the split I need on column 1 using the colon separator, but then I have to go to 'split data to columns' on the data tab to split the date field and then delete the newly created surplus column with the times in, then send it up to my Google Drive and import it into Sheets.
It should be a 10 second process but it's more like 10 to 15 minutes of fking about and it pisses me off.
One of the columns needs splitting, as it is formatted like abcd:053977 . I need the abcd bit to be on its own in one column and the 053977 bit to be on its own in another column.
Then another column is a date and time format, but I don't need the time, only the date. It is format like 2023-03-01T16:07:37Z. I only want the 2023-03-01 bit.
As there isn't a download option for the table on the html page, I've been CTRL A the whole page, opening a new text document (notepad) and pasting it in there, removing the unneeded header and footer crap and saving it as txt. I've tried saving it as csv, but of course it doesn'r put any separation character between the columns, so I then have to create my own when using the import wizard, but it only lets you set separator charater and then the whole thing messes up by changing the format back to general. This is with Sheets by the way - where I actually need it.
Current workaround is to use Excel import wizard which seems to keep the formatting after shift clicking the column headers and setting them all to text from general. This gives me the split I need on column 1 using the colon separator, but then I have to go to 'split data to columns' on the data tab to split the date field and then delete the newly created surplus column with the times in, then send it up to my Google Drive and import it into Sheets.
It should be a 10 second process but it's more like 10 to 15 minutes of f
king about and it pisses me off.egomeister said:
Doofus said:
Paste Special?
Off topic, but paste special is really annoying. Not sure if its a quirk of my system somehow, but when I paste special something into some cells and I'm looking for which is the correct formatting it will only give a couple of seconds to look before going ahead and pasting. Not being an excel guru I don't know all the icons by sight, so need to hover over them to get the names and it just overrides that! Doofus said:
egomeister said:
Doofus said:
Paste Special?
Off topic, but paste special is really annoying. Not sure if its a quirk of my system somehow, but when I paste special something into some cells and I'm looking for which is the correct formatting it will only give a couple of seconds to look before going ahead and pasting. Not being an excel guru I don't know all the icons by sight, so need to hover over them to get the names and it just overrides that! The short answer is no. Excel can never be relied upon to edit a text file without it trying to format something! I'm editing postal address data and Excel just can't resist changing every entry with a hyphen (1-2, 2-4) etc. to a date or @ symbols (annoying restaurant names) to hyperlinks,
You can save your file as a .txt and use Data Import to set each column to your preferred format. That will work until you accidentally edit an entry or want to add a helpful formula. It's also quite handy to have .csv extensions for some applications but Excel loves to open a csv and re-format everything.
You can save your file as a .txt and use Data Import to set each column to your preferred format. That will work until you accidentally edit an entry or want to add a helpful formula. It's also quite handy to have .csv extensions for some applications but Excel loves to open a csv and re-format everything.
r3g said:
I'm trying to get data in a table on a html page saved and formatted correctly so that I can import it as csv into a sql database.
One of the columns needs splitting, as it is formatted like abcd:053977 . I need the abcd bit to be on its own in one column and the 053977 bit to be on its own in another column.
Then another column is a date and time format, but I don't need the time, only the date. It is format like 2023-03-01T16:07:37Z. I only want the 2023-03-01 bit.
As there isn't a download option for the table on the html page, I've been CTRL A the whole page, opening a new text document (notepad) and pasting it in there, removing the unneeded header and footer crap and saving it as txt. I've tried saving it as csv, but of course it doesn'r put any separation character between the columns, so I then have to create my own when using the import wizard, but it only lets you set separator charater and then the whole thing messes up by changing the format back to general. This is with Sheets by the way - where I actually need it.
Current workaround is to use Excel import wizard which seems to keep the formatting after shift clicking the column headers and setting them all to text from general. This gives me the split I need on column 1 using the colon separator, but then I have to go to 'split data to columns' on the data tab to split the date field and then delete the newly created surplus column with the times in, then send it up to my Google Drive and import it into Sheets.
It should be a 10 second process but it's more like 10 to 15 minutes of fking about and it pisses me off.
Wouldn't be easier to just scrape the webpage then format the text however you need and stick it onto the database from there leaving excel out of the equation? I use c# and it would be something likeOne of the columns needs splitting, as it is formatted like abcd:053977 . I need the abcd bit to be on its own in one column and the 053977 bit to be on its own in another column.
Then another column is a date and time format, but I don't need the time, only the date. It is format like 2023-03-01T16:07:37Z. I only want the 2023-03-01 bit.
As there isn't a download option for the table on the html page, I've been CTRL A the whole page, opening a new text document (notepad) and pasting it in there, removing the unneeded header and footer crap and saving it as txt. I've tried saving it as csv, but of course it doesn'r put any separation character between the columns, so I then have to create my own when using the import wizard, but it only lets you set separator charater and then the whole thing messes up by changing the format back to general. This is with Sheets by the way - where I actually need it.
Current workaround is to use Excel import wizard which seems to keep the formatting after shift clicking the column headers and setting them all to text from general. This gives me the split I need on column 1 using the colon separator, but then I have to go to 'split data to columns' on the data tab to split the date field and then delete the newly created surplus column with the times in, then send it up to my Google Drive and import it into Sheets.
It should be a 10 second process but it's more like 10 to 15 minutes of f
king about and it pisses me off.string[] x = "abdd:1234".Split(':');
string FirstBit = x[0];
string SecondBit = x[1];
No doubt there's equivalent in whatever language you use.
I've dealt witha similar problem, and the workload I use is
Save data file as text with either comma, tab, or space delimiters
Use the data import tool and set all the column types to "text"
Split fields where required to new columns using text functions like Left and Mid. The new column data will still be treated as text.
Use the Hex2Dec function to change hex strings to numbers.
Having said that, excel's delusion that anything with a dash in it is a date (in US format!) has pissed me off for decades. And I started spreadheeting with lotus 123 & visicalc.
Does that make me old?
Save data file as text with either comma, tab, or space delimiters
Use the data import tool and set all the column types to "text"
Split fields where required to new columns using text functions like Left and Mid. The new column data will still be treated as text.
Use the Hex2Dec function to change hex strings to numbers.
Having said that, excel's delusion that anything with a dash in it is a date (in US format!) has pissed me off for decades. And I started spreadheeting with lotus 123 & visicalc.
Does that make me old?
Edited by AW111 on Saturday 3rd June 16:54
Edited by AW111 on Saturday 3rd June 17:06
AW111 said:
I've dealt witha similar problem, and the workload I use is
Save data file as text with either comma, tab, or space delimiters
Use the data import tool and set all the column types to "text"
Split fields where required to new columns using text functions like Left and Mid. The new column data will still be treated as text.
Use the Hex2Dec function to change hex strings to numbers.
Having said that, excel's delusion that anything with a dash in it is a date (in US format!) has pissed me off for decades. And I started spreadheeting with lotus 123 & visicalc.
Does that make me old?
Thanks. I always assumed you couldn't save a browser page as a text file in Windows because the only options for 'type' it lets you use in 'save as' are html, html and mhtml. However just to experiment based on what you've said, I've changed the default save file extension from .mhtml to .txt and it has actually saved it as a txt file, with comma delimiters. I've not tried importing it yet, but should eliminate 1 step of my previous process, so it's a start. Save data file as text with either comma, tab, or space delimiters
Use the data import tool and set all the column types to "text"
Split fields where required to new columns using text functions like Left and Mid. The new column data will still be treated as text.
Use the Hex2Dec function to change hex strings to numbers.
Having said that, excel's delusion that anything with a dash in it is a date (in US format!) has pissed me off for decades. And I started spreadheeting with lotus 123 & visicalc.
Does that make me old?
I do use for the HEX2DEC function for some other work, but it's not applicable in this case - I want to keep the cells as the hex value, not be converted by Excel into some random value with + signs in it.
I need to experiment with the LEFT and MID stuff.
Click top left (where I've marked in a red circle. This will select all cells in the whole sheet.
Right click to get the drop down menu and select format text.
Choose Text.
As to splitting cells - that's a different matter that all spreadsheets have a problem with if it's too complicated, but I generally select the text then do a 'text to columns' delimited by space or comma.
Right click to get the drop down menu and select format text.
Choose Text.
As to splitting cells - that's a different matter that all spreadsheets have a problem with if it's too complicated, but I generally select the text then do a 'text to columns' delimited by space or comma.
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff