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.