Value function

Having trouble with the formula below. Version 1 works and extracts what I need from a cell. I need to then convert that to a numeric value. Currently I get #invalid value. I'm missing something, and I bet it's dumb.

=MID([Active Link]@row, (FIND("/", [Active Link]@row) + 1), ((FIND("*", [Active Link]@row) - 1) - (FIND("/", [Active Link]@row))))
=VALUE(MID([Active Link]@row, (FIND("/", [Active Link]@row) + 1), ((FIND("*", [Active Link]@row) - 1) - (FIND("/", [Active Link]@row)))))

Find more posts tagged with

Formulas

Comments

Mike Wilday

Hmmm. Can you share a screenshot of the data or of sample data so we can help troubleshoot?

Preston

Here ya go. Thanks for the quick reply!

2018-12-26 15_26_45-Clipboard.jpg

Mike Wilday

This is clearly a bug. I pulled apart your formula and you are right, it works fine. Please contact support and reference the url to this community article. I would love to hear the outcome of the ticket.

What's interesting is, if I put the outcome of the first formula you provided in a column called "Helper", and then I create a new column and add peform the value on the output of your formula, it works. =value(Helper@row)

Helper is just the name of the column I chose, it could be anything...

Do you have a formula creating that active link?

Preston

Mike, I appreciate the response. I will file a support ticket and post the results. You are correct. There is a formula (see below) in the source cell I am linked to in the Active Link column. However, it does not appear to matter if the referenced cell (via formula) has a cell link or not. Again, I appreciate the input.

=IF(COUNTIF(CHILDREN([Assigned To]@row), "Change") = 0, "", COUNTIF(CHILDREN([Assigned To]@row), "Change") + " / " + SUMIF(CHILDREN([Assigned To]@row), "Change", CHILDREN([Contract Amount]@row)) + " * " + SUMIFS(CHILDREN([Contract Amount]@row), CHILDREN([Assigned To]@row), "Change", CHILDREN([Submits Approved]@row), 1))

Mike Wilday

I don't know if you can use a helper column or not in the meantime, but you could create a new column, called "helper" then add your mid formula there... then hide the column, and then do a value on the helper@row or specify the row number. Please let us know what Support says.

Preston

Mike, I barely started down the helper column path when tech support came through. Working of off Support's response, I was able to get this going in my direction by changing some things (see item #1 below for my revisions). Thanks for your help and response!

Alicia from Support (Thanks Alicia!):

In reviewing the formula 1, it appears it references some empty spaces in addition to the numbers. Please try to adjust it by changing the numbers being added/subtracted in the FIND function. In my testing it was resolved by using:

=VALUE(MID([Active Link]@row, (FIND("/", [Active Link]@row) + 2), ((FIND("*", [Active Link]@row) - 3) - (FIND("/", [Active Link]@row)))))

You my need to adjust the "2" or "3" depending upon how many spaces are in your cell.

My revisions:

=IF(FIND("/", [Active Link]@row) > 0, VALUE(MID([Active Link]@row, (FIND("*", [Active Link]@row) + 1), ((FIND("_", [Active Link]@row) - 1) - (FIND("*", [Active Link]@row))))))

2018-12-26 15_26_45-Clipboard.jpg

Paul Newcome

Glad they were able to help. I picked up on the spaces before seeing that support had gotten you squared away. I learned the hard way that "1" will work, but " 1" (with the space) won't. If there is the possibility of a space being on either side of the number you are trying to pull, you could also work in a SUBSTITUTE function (just as another option if formatting happens to change). Something along the lines of...

=VALUE(SUBSTITUTE(.........Your normal formula..........., " ", ""))

This essentially removes all of the spaces from your result before running the VALUE function.

Preston

Understood, Paul. I appreciate the insight and idea. Somethings *I* just have to learn the hard way!

Paul Newcome

Always happy to help and share.

Sign in to join the conversation!

Quick Links

Trending discussions

How to use previous cell value (from cell history) in a formula?
I have seen this question asked multiple times in the forum, but I couldn't find any clear answer provided in the answers. Please excuse me if a solution already exists, but if not, will someone please let me know if it is possible to use a previous cell value (from cell history) in a formula? I am specifically trying to…
Sheet Countdown Timer
Happy Thursday. I know many have asked this question before, but does anyone know of a way to have a column actively countdown time? Even if a plugin is needed, it will be considered. Here is what I'm looking to do. When a form is filled out and a new line is added to the sheet, I'd like the "Countdown Timer" column to…
Formula for populating date
I am needing a formula that calculates Termination Date (date property). The formula needs to look at Initiation Date (date property) and Term (text/num) which is based on # months, to calculate Termination Date. In this example, the termination date should return a value of 1/01/26. Thank you!