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.
The Convert tool also processes cells in filtered & hidden rows & columns.
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:
Click Run to see the changes.
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:
A compliment for those who work with Japanese texts :) You can convert Japanese characters from the selected cells between 3 Japanese systems:
Click Run to have all Japanese characters converted accordingly.
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.
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.
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.
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.
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 (firstname.lastname@example.org) 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 email@example.com. We keep that Google account for file sharing only and do not monitor its Inbox.
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?
I have just replied to you by email. Please check your Spam/Junk/Trash folders if you don’t see it in your Inbox.
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?
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.
I WANT TO CONVERTT A NUMBER LIKE 1.004,56 IN A EXCELL NUMBER AND I CAN´T.
CAN YOU HELP ?
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.
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 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.
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?
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.
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?
seems certain char aren't accepted in the form, my bad. so importing numbers like "32 190"
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.
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!