•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Macro to remove a decimal, but keep the decimal points?
Our take
If you're facing challenges with exporting data from accounting software in .txt format, you're not alone. When converting to .csv, the issue of handling decimal points can complicate matters, especially when your bank's system requires dollar amounts without decimals. To successfully transform values like 672.35 into 67235 for 800 rows of varying amounts, a macro can streamline this process. Let’s explore how to implement a macro that efficiently removes decimals while preserving the integrity of your data for seamless bank processing.
The data we use is exported from an accounting software and is exported as a .txt file. The bank will only accept .csv, which we can convert the .txt to. The problem is the dollar amounts export with the decimal (it's about 800 rows of data that needs to be changed), but the bank system won't accept decimals within the file. Its system will place the decimal when it processes the charges.
For example: I need to turn 672.35 into 67235 or 100.00 to 10000 (times 800 rows of varying dollar amounts).
I understand that working in a .csv is where it gets tricky and I can't find a clear answer myself on how to get this done (or if I can).
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Need macro advice! Saving longer numbers as strings to show all the charactersDesktop, Excel 365 Build 19929.20106 Click-to-Run, Beginner We have a macro that generates new files using a list of data. Sometimes that data is completely numerical with over 15 digits. Excel keeps reformatting these numerical entries using scientific notation, which screws up the output files. Right now I have to manually check the output files and reformat the affected ones so that all then hidden characters are shown. Can someone please advise on how I can update the macro to automatically convert these numbers into strings that show all the characters? The data is of variable length, but I'm pretty sure it's always under 20 characters. This problem only affects one cell of the output file. Everything else is generated without issue. It's basically the problem of "how do I make Excel macro copy-paste the entire FedEx tracking number correctly" but the data isn't consistently 16 characters and is frequently alphanumeric. EDIT: I'm open to even stupid and booleg solutions like "add a hidden text character before transferring the data" to force this to work. The output files are basically just printables. I just don't know anything about macros and do not have time to learn. submitted by /u/Umklopp [link] [comments]
- How to stop Excel from changing decimals to scientific notation?I just got a new work laptop which uses Microsoft Office 365. I regularly pull in stock prices and currency foreign exchange conversion from Yahoo! Finance into CSV, which I then copy and paste values into my Excel spreadsheet. One of the items I pull from Yahoo! Finance is IDRSGD=X, which is basically converting Indonesian Rupiah (IDR) to Singapore Dollars (SGD). Now, say IDR 1 = SGD 0.000077 and it is shown as such in Yahoo! Finance. However, in the CSV, the number automatically becomes 7.7E-5. When I copied and pasted from the CSV to my Excel file, it stayed the same way, ie. 7.7E-5. I know they mean the same thing but it's ugly and it just stands out like a sore thumb against all my other data in my spreadsheet, which are nicely presented in decimals. How can I stop Excel from automatically converting this notation? I went to Options --> Data and unchecked the relevant boxes, but it didn't help. In Excel, even if I Format Cells --> Number to reflect decimals, it does not help either. Somehow the ugly scientific notation overrides the cell formatting. There are many cells in the data set showing scientific notation instead of decimals, and I cannot be going cell by cell. I never faced this problem with my previous Microsoft Office 2019. Can someone please help? submitted by /u/Traditional_Bell7883 [link] [comments]
Tagged with
#Excel alternatives for data analysis#rows.com#generative AI for data analysis#big data management in spreadsheets#conversational data analysis#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#natural language processing for spreadsheets#digital transformation in spreadsheet software#Excel compatibility#financial modeling with spreadsheets#Excel alternatives#decimal removal#csv conversion#accounting software