Replicate publisher in column C for Google Sheets

[parvares]

Hello, starting from this sheet with column A (title, responsibility, publisher) I would need a formula that extracts only the publisher in column C. I used this formula:

= LEFT (MID (A2, FIND ("-", A2) +1, LEN (A2)), FIND (",", MID (A2, FIND ("-", A2) +1, LEN (A2))) ) -1)

but column C gives me the place of publication and the publisher. I would like instead only the publisher.

Many thanks!!

 

[parvares]

That will be difficult as there is no obvious way to determine what to return.

OK thanks! Can I ask you two questions related to these partially revised formulas in this file?
1) Is it possible to avoid that by dragging columns B, C, D the "#value!" value comes out? I would simply prefer empty cells when the original data do not appear in column A.
2) Is it possible to avoid the prefix "di" in the cells of column B relating to the author?
Many thanks again!

Formula in B1 (Author):

=MID(A2;SEARCH("Condividi";A2)+9;SEARCH("(Autore)";A2)-SEARCH("Condividi";A2)-10)

Formula in C1 (Publisher):

=LEFT(MID(A2;FIND("^";SUBSTITUTE(A2;CHAR(10);"^";LEN(A2)-LEN(SUBSTITUTE(A2;CHAR(10);""))))+1;LEN(A2));FIND(",";MID(A2;FIND("^";SUBSTITUTE(A2;CHAR(10);"^";LEN(A2)-LEN(SUBSTITUTE(A2;CHAR(10);""))))+1;LEN(A2)))-1)

Formula in D1 (Date):

=RIGHT(A2;LEN(A2)-FIND(",";A2)-1)

 

[Fluff]

For point 1 you can use

=IF(A2="","",some formula)

 

[parvares]

Great! Is there a solution for the second question?

 

[Fluff]

You could use

=substitute(MID(A2;SEARCH("Condividi";A2)+9;SEARCH("(Autore)";A2)-SEARCH("Condividi";A2)-10),"di ","")

but if any words end with di then it would be removed.

 

[parvares]

Ah ok... Isn't it there a formula similar to this one?

=MID(A2;SEARCH("Condividi di";A2)+9;SEARCH("(Autore)";A2)-SEARCH("Condividi di";A2)-10)

 

[parvares]

... I mean entering "Condividi di" in search function...

=MID(A2;SEARCH("Condividi di";A2)+9;SEARCH("(Autore)";A2)-SEARCH("Condividi di";A2)-10)

 

[parvares]

Hello, maybe I found the solution. Is it possible to convert this formula for MS Excel? Thanks

Google Sheets

=REGEXEXTRACT(SUBSTITUTE(A2;"(Autore)";"#");"di (.*)#")

LibreOffice Calc

=IFERROR(TRIM(SUBSTITUTE(REGEX(SUBSTITUTE(SUBSTITUTE(B2;"di";"#");"(Autore)";"#");"# .* #");"#";""));"")

 

[Fluff]

Fraid not, Excel does not have a Regex function.

 

[parvares]

Mhm, okay, thanks!