Change data format in Google Sheets
Power Tools provides you with various utilities to speed up most of your daily tasks. The Convert group of tools will help you make number and date formatting in your range uniform. You can also use this group to convert the data to JSON or XML format, and between the 3 most commonly used Japanese systems.
Before you start
The Convert tool also processes cells in filtered & hidden rows & columns.
How to convert data format in a Google spreadsheet
Run the Convert tool
- Go to Extensions > Power Tools > Start in the Google Sheets menu:
- Click on the Convert icon on the add-on sidebar:
Convert data formats and number signs
The first group lets you convert data formats and number signs. Select the required cells in your sheet and choose to do one of the following:
- Convert text to numbers
For when numbers formatted as text mix up the sorting or calculation results in your sheet. - Convert text to dates
Recognize dates in the selected cells that are formatted as text and switch them to date format. - Convert number sign
This one will replace negative numbers with positive and back within the selected range. Just pick the necessary option from the drop-down list: Convert negative numbers to positive or Convert positive numbers to negative to change the number signs to the opposite. Or have it both ways at once by picking Reverse all number signs.
Click Run to see the changes.
Convert range to JSON/XML
Pick one of the radio buttons in this group — Export to JSON or Export to XML — to get the entire selected range exported to one of the corresponding formats:
Click Run and you will get a pop-up window with the results you can copy:
Convert Japanese characters
A compliment for those who work with Japanese texts :) You can convert Japanese characters from the selected cells between 3 Japanese systems:
- Convert characters to Hiragana from full-width or half-width Katakana.
- Convert characters to full-width Katakana from half-width Katakana or Hiragana.
- Convert characters to half-width Katakana from full-width Katakana or Hiragana.
Click Run to have all Japanese characters converted accordingly.
Responses
Hans says:
February 1, 2021 at 11:52 am
Hi,
Great tool for converting the imported Google Analytics (ga:transaction) data in Google sheets that are first imported as "text" data. But is there a way to automate the conversions from text to numbers daily on this Google sheet? Tried creating a macro, but I got an error?
The GA script on Google sheets is run daily, but this overwrites the Power tools (conversion from text to numbers) again and again.
Kind regards,
Hans
Hi Hans,
Thank you for your feedback. You see, our add-ons cannot be run from macros and scripts. To start the conversion operation quickly, just add it to the list of your Recent / Favorite tools at the bottom of the add-on.
Martin says:
December 11, 2020 at 8:43 am
I want to convert, for example, "485.2M" or "1.02B" to whole numbers (where M = 1 million and B - 1 billion). How can I do this?
Thank you for your comment, Martin. Unfortunately, we do not have such tool now but I will pass your feature request on to our developers. Perhaps, they will consider the possibility of implementing this feature some day.
Time sheets under any circumstance will not sum my time, 1 plus 2 stuff. Tried converting with add one nothing. I says:
October 1, 2020 at 6:52 pm
imported a csv file to populate Time sheets, time is in tenthsm of ann hour, just arithmetic. Put the sum formula. Tried the convert from add on tool box. Excel it is not. Do I have ton convert before populating? The data got jumbled and in trying to change the numbers the revert back.
Hello!
Thank you for your message. If you would like us to see whether our software can help you to cope with your task, please share a small sample spreadsheet with us (support@apps4gs.com) with a copy of your source data and the expected result. Please shorten your tables to 10-20 rows. If there is any confidential information in your records, you can replace it with some irrelevant data, just keep the format.
Please do not email to support@apps4gs.com. We keep that Google account for file sharing only and do not monitor its Inbox.
Sara says:
August 10, 2020 at 10:11 pm
I was able to convert the date but it's converting everything in 1900s not 2000s.
Ex: converts into 1920 instead of 2020~
Can you please help?
Hello Sara,
I have just replied to you by email. Please check your Spam/Junk/Trash folders if you don’t see it in your Inbox.
RICHARD ALCORN says:
July 13, 2020 at 7:50 pm
I'm trying to convert a column of debits that transferred into Sheets as text, with a $ in front. The conversion to numbers worked on positive values, but not on negative values, i.e. -$150.00 will not convert to numeric format and therefore, can't be sorted as a number. Any thoughts?
Hello RICHARD,
Thank you for reporting the issue. Indeed, due to the currency symbol our add-on treats this number as a text string. We will do our best to improve the add-on work asap. Thank you once again.
MADALENA says:
February 18, 2020 at 5:25 pm
I WANT TO CONVERTT A NUMBER LIKE 1.004,56 IN A EXCELL NUMBER AND I CAN´T.
CAN YOU HELP ?
Hello Madalena,
Thank you for contacting us. If you work in Excel and have such a problem with your numbers, you need to update your Regional Settings and set coma as a Decimal symbol and dot as a Digit grouping symbol. I've sent you a screenshot via email so that you could understand what I mean better.
Apply these changes, restart your Excel and the values like 1.004,56 will be considered as numbers. If this is not exactly what you meant, please specify. Thank you.
Jeff says:
October 21, 2019 at 4:15 pm
Hello,
Is there (or will there be) an option to convert both negatives and positives to their opposite state at the same time?
I currently have to covert each set of data on it's own, instead of the entire column of numbers at one time.
Thank You,
- Jeff
Hello Jeff,
Thank you for your comment.
We have just replied to you via email, please check your Spam/Junk/Trash email folders if you still don’t see our email in your Inbox. Thank you.
Charles says:
May 29, 2019 at 1:38 pm
Hello, Natalia,
I tried to convert a text 1:36.48 (one minute, thirty six second & 48 one hundredths of a second) into a number using your product, but no luck. Any suggestions?
Thanks
Hello Charles,
Thank you for contacting us.
For our tool to convert your data, it should look in a way for Google to understand what it is. Your type of record cannot be coerced into a number/date because it's pure text and even custom date formats don't support such numbers.
I can only advise you to take a look into the standard Duration format which requires to use seconds and milliseconds. Perhaps, it will help you solve the task.
Sorry I can't assist you better.
Alexandre Caya says:
October 8, 2018 at 3:10 pm
Seems your tool isn't working for converting text to numbers. When importing numbers like it is treated as text because there's a space between the 1000s, and your tool doesn't do anything about that. Does it only convert the cell type from text to number? If that's it, then google base functions does it too?
Alexandre Caya says:
October 8, 2018 at 3:11 pm
seems certain char aren't accepted in the form, my bad. so importing numbers like "32 190"
Hello, Alexandre,
Thank you for your interest in our product!
Our tool turns records into numbers the same way Google does. In other words, when you try to convert records like "32 190" written with a space, neither Google nor our add-on will apply the number format. The value will remain as "32 190".
I can suggest you run Remove all spaces utility from Power Tools. The numbers will then be recognized and you'll be able to convert them.
IB says:
October 5, 2018 at 6:00 pm
This is by far the best and easiest solution for turning negative numbers into positive numbers on google sheet. Gosh I have spent hours trying to figure this out. Glad this add on is here, 10x easier than any other option.
Thanks for your lovely feedback!
We're happy to know our add-in is of great use to you! :)
Post a comment
Seen by everyone, do not publish license keys and sensitive personal info!
If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.
