# Converting "-07" text to "-7" number value in google sheets **URL:** \ **Category:** Ask for Help\ **Created:** [May 9, 2020, 12:14am UTC](https://community.glideapps.com/t/converting-07-text-to-7-number-value-in-google-sheets/8770 "2020-05-09T00:14:25Z")\ **Posts on this page:** 18\ **Page:** 1
**Author:** ![ray\_d](https://avatars.discourse-cdn.com/v4/letter/r/ea5d25/32.png) [@ray\_d](https://community.glideapps.com/u/ray_d)\ **Post date:** [May 9, 2020, 12:14am UTC](https://community.glideapps.com/t/converting-07-text-to-7-number-value-in-google-sheets/8770/1 "2020-05-09T00:14:25Z")
Anyone know how to convert “-07” text to “-7” number value in google sheets? Been hitting a wall with this, can’t find the solution on Google. here’s the error i get ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/2/2f55477242810acde8837d80a6680889114a820e.png) I tried =text(A1,“#”) but doesn’t seem to work. It spits out the same value with the leading zero “-07” I’m trying to do a calculation using a column of numbers that have been stripped out of a string eg. “-07” stripped from UTC-07 ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [May 9, 2020, 12:28am UTC](https://community.glideapps.com/t/converting-07-text-to-7-number-value-in-google-sheets/8770/2 "2020-05-09T00:28:27Z")
Here’s your solution: ``` =VALUE("-07") ``` ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/1/136c0a7c2d6b2c295dfde8566e67f604e57211ac.png) Edit: Proof that it is a number and you can do calculation with it: ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/e/e11181bbeca9193e50f6f1048bc88b064be56345.png) ---
**Author:** ![ray\_d](https://avatars.discourse-cdn.com/v4/letter/r/ea5d25/32.png) [@ray\_d](https://community.glideapps.com/u/ray_d)\ **Post date:** [May 9, 2020, 12:39am UTC](https://community.glideapps.com/t/converting-07-text-to-7-number-value-in-google-sheets/8770/3 "2020-05-09T00:39:39Z")
Thanks, but the source I’m getting the “-07” from is taken from a formula in another cell. I can’t put “-07” directly into the end formula ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/9/9deff353bc75e65ee5302bb5e29def78267233db.png) ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/5/56e3d33478d36bec8ba69a124a80c5e6c6067a01.png) ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/1/1f9517dc4fa8e0eba099d6a38137464cfc54ef90.png) ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [May 9, 2020, 12:45am UTC](https://community.glideapps.com/t/converting-07-text-to-7-number-value-in-google-sheets/8770/4 "2020-05-09T00:45:49Z")
Just pass VALUE right into your ARRAYFORMULA. Also, it’s my personal preference to make an array like this in your header row so that you won’t accidentally delete your formula. Hope it helps, Ray. ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/5/565ae10e6998e8ee2c5bb4b1029dc4e1ec937227.png) ---
**Author:** ![ray\_d](https://avatars.discourse-cdn.com/v4/letter/r/ea5d25/32.png) [@ray\_d](https://community.glideapps.com/u/ray_d)\ **Post date:** [May 9, 2020, 12:56am UTC](https://community.glideapps.com/t/converting-07-text-to-7-number-value-in-google-sheets/8770/5 "2020-05-09T00:56:16Z")
Thanks! Really love that trick of putting it in the header row! I inputted it: ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/b/b95197066ae394f123f22ee921801d7225d5a750.png) But get the same error: ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/2/238e6234bdbdea880d1e07e0c5a68c1ca804e679.png) It still thinks the number is text for some reason ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [May 9, 2020, 1:00am UTC](https://community.glideapps.com/t/converting-07-text-to-7-number-value-in-google-sheets/8770/6 "2020-05-09T01:00:42Z")
Can you message me the link of the sheet or a copy of it so I can have a look? Thanks. ---
**Author:** ![ray\_d](https://avatars.discourse-cdn.com/v4/letter/r/ea5d25/32.png) [@ray\_d](https://community.glideapps.com/u/ray_d)\ **Post date:** [May 9, 2020, 3:19am UTC](https://community.glideapps.com/t/converting-07-text-to-7-number-value-in-google-sheets/8770/7 "2020-05-09T03:19:02Z")
made a copy with sample data > **[sample sheet - text to number](https://docs.google.com/spreadsheets/d/1KqyzhbsclvzeotB9mynoX2Iaaaz03eJKYnHDlGUI-Mc/edit?usp=sharing)** > > Sheet1 > > Time Zone,UTC Offset,UTC Real Value > \[AST\] Atlantic Standard Time (UTC−04),−04,-4 > \[CDT\] Central Daylight Time (North America) (UTC−05),−05,-5 > \[CST\] Central Standard Time (North America) (UTC−06),−06,-6 > \[EDT\] Eastern Daylight Time (North... thanks for looking into it! ---
**Author:** ![Wiz.Wazeer](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/wiz.wazeer/32/72423_2.png) [@Wiz.Wazeer](https://community.glideapps.com/u/Wiz.Wazeer)\ **Post date:** [May 10, 2020, 1:53pm UTC](https://community.glideapps.com/t/converting-07-text-to-7-number-value-in-google-sheets/8770/8 "2020-05-10T13:53:26Z")
Like how you seem to have a quick answer to formula problems. ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [May 10, 2020, 2:03pm UTC](https://community.glideapps.com/t/converting-07-text-to-7-number-value-in-google-sheets/8770/9 "2020-05-10T14:03:00Z")
Thanks Wiz, I work everyday with Google Sheets (was a Business Intelligence Analyst in my last job, pretty much Google Sheets & SQL all day). ---
**Author:** ![Wiz.Wazeer](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/wiz.wazeer/32/72423_2.png) [@Wiz.Wazeer](https://community.glideapps.com/u/Wiz.Wazeer)\ **Post date:** [May 10, 2020, 2:04pm UTC](https://community.glideapps.com/t/converting-07-text-to-7-number-value-in-google-sheets/8770/10 "2020-05-10T14:04:54Z")
Next time, if anyone has formula problem I know who to signpost them to. 😊 ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [May 10, 2020, 2:09pm UTC](https://community.glideapps.com/t/converting-07-text-to-7-number-value-in-google-sheets/8770/11 "2020-05-10T14:09:36Z")
My pleasure to help! Just tag, message or drop me an email at [ariesarsenal@gmail.com](mailto:ariesarsenal@gmail.com)! Have a nice day 😌 ---
**Author:** ![Wiz.Wazeer](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/wiz.wazeer/32/72423_2.png) [@Wiz.Wazeer](https://community.glideapps.com/u/Wiz.Wazeer)\ **Post date:** [May 10, 2020, 2:10pm UTC](https://community.glideapps.com/t/converting-07-text-to-7-number-value-in-google-sheets/8770/12 "2020-05-10T14:10:11Z")
Tx a lot. 🙂 ---
**Author:** ![Drearystate](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/drearystate/32/49605_2.png) [@Drearystate](https://community.glideapps.com/u/Drearystate)\ **Post date:** [May 10, 2020, 2:36pm UTC](https://community.glideapps.com/t/converting-07-text-to-7-number-value-in-google-sheets/8770/13 "2020-05-10T14:36:13Z")
Was this resolved? ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [May 10, 2020, 2:59pm UTC](https://community.glideapps.com/t/converting-07-text-to-7-number-value-in-google-sheets/8770/14 "2020-05-10T14:59:02Z")
Yes it did. Ray and I later solved it via a chat, my method worked, not sure what happened with his sheet initially but after a refresh it worked. ---
**Author:** ![Drearystate](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/drearystate/32/49605_2.png) [@Drearystate](https://community.glideapps.com/u/Drearystate)\ **Post date:** [May 10, 2020, 3:52pm UTC](https://community.glideapps.com/t/converting-07-text-to-7-number-value-in-google-sheets/8770/15 "2020-05-10T15:52:02Z")
Gotcha, if it wasn’t I was going to give him a quicker way to resolve, but good job 🙂 ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [May 10, 2020, 4:03pm UTC](https://community.glideapps.com/t/converting-07-text-to-7-number-value-in-google-sheets/8770/16 "2020-05-10T16:03:16Z")
If you have another method, feel free to post it. Just another workaround that may help us in the future. Appreciated 😁 ---
**Author:** ![ray\_d](https://avatars.discourse-cdn.com/v4/letter/r/ea5d25/32.png) [@ray\_d](https://community.glideapps.com/u/ray_d)\ **Post date:** [May 10, 2020, 5:10pm UTC](https://community.glideapps.com/t/converting-07-text-to-7-number-value-in-google-sheets/8770/17 "2020-05-10T17:10:18Z")
I would like to see any other solution you have 🙂 ---
**Author:** ![Drearystate](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/drearystate/32/49605_2.png) [@Drearystate](https://community.glideapps.com/u/Drearystate)\ **Post date:** [May 10, 2020, 5:13pm UTC](https://community.glideapps.com/t/converting-07-text-to-7-number-value-in-google-sheets/8770/18 "2020-05-10T17:13:01Z")
I was going to suggest setting up a page that had the list of timezones and just set next to them the number needed, then simply vlookup those as needed into a formula. There are a few other ways but if you didn’t have someone directly working on it this would of been the easiest way to explain a solution. This could of been done with relation and lookup through glide as well if it was requiring a page that needed to continuously add data.