Identification of the position of a word in a text string in Calc

[ROSt53]

I have a Calc sheet and in a column text strings like:

text1 and text2

text1 and text2 have no space & before and after “and” there is 1 space

I want to create a formula (or apply several) to place text1 in once cell and text2 in the neighboring cell. The “and” and the space are not needed.

I checked the Calc’s text formulas but could not identify one which can help me.

Identification of the number of charactes until the “a” and “d” of “and” would be already of help.

Thanks in advance!

[ROSt53]

LibO 4.0.4 on XP

[oweng]

@karolus has provided the most easy answer / solution. If however you absolutely want to use text functions then this pair (given “text1 and text2” in A1) will give you what you want:

=LEFT(A1;SEARCH("[^ ][ ]";A1;1))    # result is "text1"
=RIGHT(A1;SEARCH("[ ]and[ ]";A1;1)) # result is "text2"

[ROSt53]

@oweng - also thanks to you for the SEARCH function. I expected that something like this must exist. Next time I read the help wiki. There the explanations are better - for me. Reading the description of the function (after clicking on fx), the third parameter “position” made me thinking in the wrong way.