Converting "-07" text to "-7" number value in google sheets

[ray_d]

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

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

[ThinhDinh]

Here’s your solution:

=VALUE("-07")

Edit: Proof that it is a number and you can do calculation with it:

[ray_d]

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

[ThinhDinh]

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.

[ray_d]

Thanks! Really love that trick of putting it in the header row!

I inputted it:

But get the same error:

It still thinks the number is text for some reason

[ThinhDinh]

Can you message me the link of the sheet or a copy of it so I can have a look? Thanks.

[ray_d]

made a copy with sample data

sample sheet - text to number

thanks for looking into it!

[Wiz.Wazeer]

Like how you seem to have a quick answer to formula problems.

[ThinhDinh]

Thanks Wiz, I work everyday with Google Sheets (was a Business Intelligence Analyst in my last job, pretty much Google Sheets & SQL all day).

[Wiz.Wazeer]

Next time, if anyone has formula problem I know who to signpost them to.

[ThinhDinh]

My pleasure to help! Just tag, message or drop me an email at ariesarsenal@gmail.com!

Have a nice day

[Wiz.Wazeer]

Tx a lot.

[Drearystate]

Was this resolved?

[ThinhDinh]

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.

[Drearystate]

Gotcha, if it wasn’t I was going to give him a quicker way to resolve, but good job

[ThinhDinh]

If you have another method, feel free to post it. Just another workaround that may help us in the future. Appreciated

[ray_d]

I would like to see any other solution you have

[Drearystate]

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.