Hello, I want to extract a partial value from a cell.
The original value is composed as follow “48.593345, -123.399607”.
I have no problem to extract the left portion of the value. However I have a problem to extract dynamically the right value.
A spreadsheet with same examples is attached.
Scenario #1 - #5 are no problem.
And based and these scenarios (#1-#5) I build the formula for Scenario #6.
But for some reason, I don’t know why, it is not working as I expected.
Perhaps someone smarter as I am can explain what I am doing wrong.
Thanks for any reply.
PS. Why can’t I attach a screenshot and insert it into my posting in this forum?
NB After you gave save your screenshot, you can drag it from your file manager onto the open editing window or you can click the 7th icon with tooltip Upload to navigate to and insert the image.
I would suggest to use Regular Expressions.
See attached example: disask123656_ExtractionsRe.ods (34.8 KB)
(Caution! it’s teacher style.)
BTW:
In most cases example documents are much better than screenshots.
The example can then also contain a little image (crop of a screenshot?) if helpful.
Thanks for the provided solution.
That’s really a cool resolution for the problem.
However I’m a little bit scratching my head, why?
a) it seems that the formula in column ‘F’ (label ‘part1’) and in col ‘G’ (label ‘part 2’) are the same (except the 4th parameter), however the extracted part is different.
b) the formula in col H (‘part3’) is also ‘identical’ to the one in col ‘F’, however the result is listed as “#N/A”
c) the same can be said to the formula in col ‘I’ (‘part4’).
Why is that?
Care to share?
The REGEX() function is powerful, but as -often is the case with powerful tools- not exactly simple to use.
It’s made to serve 2 different purposes:
Find and replace (as the F&R tool does if the option Regular expressions is enabled.
In this case the third parameter is a replacement string which again doesn’t contain only literals, but also parts with a special semantic.
The fourth parameter gives in this case the ordinal of the finding which is to be replaced, or "g" for Replace all occurrences.
Return an extracted occurrence.
In this case the third parameter is omitted (2 consecutive semicolons).
The fourth parameter gives now the ordinal of the occurrence to be returned.
(a) Yes. See point 2. above. These numbers are taken from referenced cells in the example and cover there the range from first to fourth.
(b) If a formula and its subexpressions have correct syntax and references, but can’t return a result for a “semantical” reason -in the example if a third or a fourth occurence don’t exist- it returns an error message, in the given case #N/A meaning “not available!”. You can use the function IFNA() to get returned something different in this case.
(c) See (b).
(I would prefer to have 2 functions for the different purposes or a mode parameter, and to avoid a case where the missing of a parameter is interpreted as a positive information. The only additional case of the kind I have in mind is INDEX() where also the chosen name is misleading.)
no real challenge then;
anyhow, to avoid easy mistakes → Information Functions - FORMULA()
Displays the formula of a formula cell as a text string.
This function is always recalculated whenever a recalculation occurs.
and definetely don't bother parsing text cells -> https://wiki.documentfoundation.org/Faq/Calc/001 - How do I open a text file containing separate fields in a spreadsheet?
