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))
Thanks
Code: Select all
=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(G2;FIND("|";SUBSTITUTE(G2;"|";"|";2))+1;LEN(G2));"|";REPT(" ";LEN(G2)));LEN(G2)));TRIM(G2))
Code: Select all
...SUBSTITUTE(G2;"|";"|";2)...
Code: Select all
Code: Select all
Code: Select all
...SUBSTITUTE(G2;"|";"|";2)...
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))))
Code: Select all
=INDEX(TEXTSPLIT("|"; ;G2);IF(INDEX(CURRENT();2)="";1;2))
Code: Select all
=IFERROR(MID($G2;FIND("|";$G2)+1;-CURRENT()+FIND("|:";SUBSTITUTE(G2;"|";"|:";2)));$G2)
GOOD NEWSLupp wrote: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?