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.

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

Reply

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.

Reply

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

Reply

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.

Reply

I WANT TO CONVERTT A NUMBER LIKE 1.004,56 IN A EXCELL NUMBER AND I CAN´T.
CAN YOU HELP ?

Reply

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.

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

Reply

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.

Reply

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?

Reply

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.

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

Reply

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.

Reply

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.