Hey everyone! I’ve got a bit of a problem with some GIS data I moved to Google Sheets. The coordinate numbers are super long with tons of decimal places. I’m trying to figure out how to quickly chop off everything after the decimal point. Not just hide it, but actually get rid of it for good.
For example, I want to turn something like 987654.3210 into just 987654. Any ideas on how to do this fast? I’ve got a bunch of cells to fix and don’t want to spend all day on it.
I tried using the ROUND function, but that’s not quite what I need. Is there a way to just keep the whole number part? Maybe some kind of formula or script? Thanks for any help you can give!
yo try the floor function. it’ll chop off decimals. use =FLOOR(A1) and drag down. copy-paste as values to keep things permanent. remember to backup original data in case of errors. good luck!
I’ve dealt with similar issues when working with large datasets. The INT function is your best bet here. It returns the integer part of a number, effectively chopping off everything after the decimal point.
Try this formula: =INT(A1)
Replace A1 with the cell containing your coordinate. You can drag this formula down to apply it to multiple cells quickly.
If you need to permanently replace the values, you can copy the results and paste them as values over the original data. Just be sure to keep a backup of your original data in case you need those decimals later.
For bulk operations, you might consider using Google Apps Script. It’s a bit more advanced, but it can process large amounts of data much faster than manual formulas.
The TRUNC function is precisely what you need for this task. It truncates numbers to a specified number of decimal places without rounding. In your case, you’d use it like this:
=TRUNC(A1)
This will remove everything after the decimal point, keeping only the whole number part. You can apply this formula to your entire column of coordinates, then copy and paste as values to permanently remove the decimals.
For a quicker method with large datasets, consider using Find and Replace. In the Find field, enter .\d+ and make sure ‘Search using regular expressions’ is checked. Leave the Replace field empty. This will strip all decimal points and following digits in one go.