[Solved] Office formula to OpenOffice Calc?

[wzshop]

Hi,
I have this excel formula:

Code: Select all

=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(G2;FIND("|";SUBSTITUTE(G2;"|";"|";2))+1;LEN(G2));"|";REPT(" ";LEN(G2)));LEN(G2)));TRIM(G2))

However this is not working in open office. What is wrong?
Thanks

[Zizi64]

The function IFERROR() is not exist in the OpenOffice, but it exists in the LibreOffice
Use the LibreOffice or combine the ISERROR() function and the IF() statement in the OpenOffice.

[JohnSUN-Pensioner]

When you will change the formula in accordance with the recommendation of Zizi64, read it again - perhaps the same result can be obtained in an easier way. I am confused with this part

Code: Select all

...SUBSTITUTE(G2;"|";"|";2)...

What was it supposed to do?

[wzshop]

Thanks,
So would it then be:

Code: Select all

=IF(ISERROR(TRIM(LEFT(SUBSTITUTE(MID(G2;FIND("|";SUBSTITUTE(G2;"|";"|";2))+1;LEN(G2));"|";REPT(" ";LEN(G2)));LEN(G2))));TRIM(G2))

Code: Select all

? Because that is not working...

Code: Select all

...SUBSTITUTE(G2;"|";"|";2)...

I am not sure. Someone wrote the function for me. But when G2 the second value is empty, the the first value should be copied.

[Zizi64]

Please upload your ODF type sample file here.

[wzshop]

Here you go see column f

Explaining formula: i am searching for the second value (after the first comma). But if no second value is given i need the first value.

[Zizi64]

Here is an another approach of the problem:

sample_Zizi64.ods

(11.82 KiB) Downloaded 79 times

[wzshop]

Thanks this is however not working when just 1 value is given. See the example.

[JohnSUN-Pensioner]

It seams to me that you lookup something like as

Code: Select all

=IF(ISERROR(TRIM(LEFT(SUBSTITUTE(MID(G2;FIND("|";SUBSTITUTE(G2;"|";"|";2))+1;LEN(G2));"|";REPT(" ";LEN(G2)));LEN(G2))));TRIM(G2);TRIM(LEFT(SUBSTITUTE(MID(G2;FIND("|";SUBSTITUTE(G2;"|";"|";2))+1;LEN(G2));"|";REPT(" ";LEN(G2)));LEN(G2))))

[wzshop]

Thanks, that seems to be working in libreoffice, not in open office...

[JohnSUN-Pensioner]

Indeed? Please check again

[wzshop]

That is weird, see attachment

[wzshop]

Hmm it does work in ods format. But i need the format to stay csv.. then it won't work;) weird...

[JohnSUN-Pensioner]

Oops! And SOURCE file is CSV too?

[wzshop]

See here.. this is the file i have.. It needs to stay csv but i am using OO since office is doing the weirdest tricks on numbers in my spreadsheet.

[JohnSUN-Pensioner]

Oh my dear friend! We have all the time solved the wrong task
As far as I understand, you have the source CSV-file, in which each line contains information about the product for the online store - SKU, Title, Qty, Price, etc., etc., and ImageURL.
Your problem is that all the fields in this file are separated by commas (or a semicolon, or tab), but links to individual images in the ImageURL are separated by a pipe.
For the output file you need to get each of the URL in a separate field and if some field is left empty, then fill it with the very first, main URL.
In addition, you will most likely need to skip some lines of the source file (with an empty price or zero number or bad SKU)
Perhaps you need to somehow change (usually increase) the price. Perhaps you need to add some columns that were not in the source file (for example, delivery conditions).
And all this you want to do with the help of formulas and manual edits of the resulting table. The final result must be saved to another СSV-file.
Do I understand your task correctly?

[wzshop]

Thanks for getting back to me.
I need the second value to be separated, but if none was found i just need the first value to be separated.
Anyway i managed it by converting the csv to ods, use the formula and convert it back to csv again.
All worked out fine!
Thanks!

[Lupp]

Three questions:
-0- Who did JOIN the multiple URL - and for what reason? Bad design of a source you depend on? Why don't you SPLIT using the 'Text to Columns...' tool?
-1- You are talking of commas I cannot find in the given URL cells. Are you meaning the vertical-bar-character (aka pipe) "|"?
-2- This is one of the not too few cases where a TEXTSPLIT function would make things much simpler. Unfortunately neither Excel nor Calc provide such a function. A very simple implementation in OpenOffice BASIC would be powerful enough in this case. Can you accept a solution relying on user code? IF so, a simple formula like

Code: Select all

=INDEX(TEXTSPLIT("|"; ;G2);IF(INDEX(CURRENT();2)="";1;2))

would do the trick. (See attachment! You need to permit "macros" to get this part working.)

And an answer not relying on a user function, but only working in versions of LibO having implemented the IFERROR function:

Code: Select all

=IFERROR(MID($G2;FIND("|";$G2)+1;-CURRENT()+FIND("|:";SUBSTITUTE(G2;"|";"|:";2)));$G2)

This solution assumes you can asure:
-a- There is no empty segment between "pipes" or behind the last "pipe".
-b- There is no sequence "|:" contained in one of the original URL. (Should be factual if "URL" isn't just a heading.)

If you insist on neither relying on user code nor shifting to LibO you may follow the advice by "JohnSUN-Pensioner". I would prefer to insert a helper column to get things clearer.
This way is also demonstrated in the attachment on 'Blad2'.

(Editing:)

(Oh my god! Scrap it! I just read the posts made by the OQ and JohnSUN again while I wrote my own post and was off for a while. It's a pity! I should have posted the question -0- in advance and waited for an answer. Besides of a bit of fun when working on solutions, contributing to forums like this one is mainly wasting time.)

Ask the true, relevant, actual question, please. Don't ask for steps on a way which is probably basically not advisable. I step into this trap again and again.

[Villeroy]

This is one of the not too few cases where a TEXTSPLIT function would make things much simpler. Unfortunately neither Excel nor Calc provide such a function. A very simple implementation in OpenOffice BASIC would be powerful enough in this case. Can you accept a solution relying on user code?

GOOD NEWS
[Calc][oxt] A function for all python string methods

Someone mapped Python string functions to one Calc function called PYSTRING.
For instance, Python function "split" is documented like this: my_string.split(sep,max) and returns an array of strings split from my_string. sep is the separator (default space), max is the maximum of splits to return (default unlimited, the last element is the rest of the string unsplit).
Mandatory documentation is this: https://docs.python.org/2.7/library/std ... #str.split

After installing the add-in, you call =PYSTRING("split";my_string;sep;max) as array function since you expect more than one value as a result.
Concrete:
=PYSTRING("split";A1) to split the string in A1 by spaces
=PYSTRING("split";A1;"/") to split the string in A1 by slashes
=PYSTRING("split";A1;"/";2) to split the string in A1 by slashes and return only the first 2.

in your attached spreadsheet a working formula would be:
=INDEX(PYSTRING("split";G2;"|");IF(COUNTA(CURRENT())=1;1;2))
The second element never equals "". It is missing. Threrefore we test COUNTA(CURRENT())
=INDEX(PYSTRING("split";G2;"|");COUNTA(CURRENT())) returns the last element

The same schema applies to 43 more Python functions other than split. All of them are documented in the example spreadsheet with links to the original Python documentation. If you learn how to translate the original documentation str.function(arg1, arg2, arg3) into =PYSTRING(function, str, arg1, arg2, arg3) and if you know how to deal with array functions, you get 44 new text functions.