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.

How to convert data format in a Google spreadsheet

  1. Go to Add-ons > Power Tools > Start in the Google Sheets menu:
    Start Power Tools.
  2. Click on the Convert icon on the add-on's sidebar:
    Click the Convert icon on the Power Tools sidebar.

    Tip. You can pick Add-ons > Power Tools > Convert on the first step to see the tool's options right away.
  3. Select the cells you'd like to convert and choose between the following options:
    Choose the option depending on the way you want to change the selected data.

    • 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.
    • Export range to
      Check this option and pick JSON or XML format from the drop-down list to get the range in one of them. You will get a pop up window with the results you can copy:
      Export your data to XML format.

Click Convert and the changes will be applied to the range you selected.

Responses

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.

Reply

Thanks for your lovely feedback!
We're happy to know our add-in is of great use to you! 🙂

Reply
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?

Reply
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"

Reply

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.

Ask a question (posted publicly)

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.
Privacy policy Terms of use Contact us

Microsoft and the Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries.