Simple automate script mangles number formatting
I have a new laptop and I thought – i/o moving my macro – to create an Automated script.
I recorded the script with the same steps as the macro:
Select column A > data > text to column > Delimited on | sign > finish.
Attached is the Automation script after my first change: adjust the destination from B1 to A1, because otherwise a whole csv string remained in A1, and the the first item of the headers started in B2 i/o A1. Strangely enough the rest of the rows were filled correctly, with the first item in column A.
Later (after taking the screenshot) I also changed the range to A:A rather than a specified number of rows.
After the changes the script seemed to work as far as the Text to Column bit went. However: something strange happened with the numerical data in my ‘price’ column (U). By the way, my Excel regional settings have a comma separator.
The flat data in the csv contains this item in the form of 4 decimals. So 0 > 0,0000.
And actually the 0,0000 is the only one that remains the same after running the script.
All other amounts – while also starting out as a number with 4 digits after the comma – end up being translated as follows: 3,5500 becomes 35.500
I don’t understand why the script would cause this deviation., and frankly this experience doesn’t encourage playing around with it.
I have a new laptop and I thought – i/o moving my macro – to create an Automated script.I recorded the script with the same steps as the macro:Select column A > data > text to column > Delimited on | sign > finish. Attached is the Automation script after my first change: adjust the destination from B1 to A1, because otherwise a whole csv string remained in A1, and the the first item of the headers started in B2 i/o A1. Strangely enough the rest of the rows were filled correctly, with the first item in column A.Later (after taking the screenshot) I also changed the range to A:A rather than a specified number of rows. After the changes the script seemed to work as far as the Text to Column bit went. However: something strange happened with the numerical data in my ‘price’ column (U). By the way, my Excel regional settings have a comma separator.The flat data in the csv contains this item in the form of 4 decimals. So 0 > 0,0000.And actually the 0,0000 is the only one that remains the same after running the script.All other amounts – while also starting out as a number with 4 digits after the comma – end up being translated as follows: 3,5500 becomes 35.500 I don’t understand why the script would cause this deviation., and frankly this experience doesn’t encourage playing around with it. Read More