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

  1. Go to Extensions > Power Tools > Start in the Google Sheets menu:
    Start Power Tools.
  2. Click on the Convert icon on the add-on sidebar:
    Click the Convert icon on the Power Tools 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 data formats and number signs.

  • 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:
Export range to JSON or XML.
Click Run and you will get a pop-up window with the results you can copy:
Export your data to the XML format.

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 Japanese characters.

  • 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

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.

Aksana Pachkouskaya (Ablebits Team) says:
October 1, 2020 at 8:06 pm

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.

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?

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 ?

Mary Trifuntova (Ablebits Team) says:
February 18, 2020 at 5:47 pm

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

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

Natalia Sharashova (Ablebits Team) says:
May 29, 2019 at 2:23 pm

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"

Natalia Sharashova (Ablebits Team) says:
October 9, 2018 at 12:53 pm

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.

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.