Ask Your Question

Identification of the position of a word in a text string in Calc [closed]

asked 2013-06-27 09:53:53 +0200

ROSt52 gravatar image

updated 2015-11-02 23:24:43 +0200

Alex Kemp gravatar image

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!

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-02 23:25:01.098088


LibO 4.0.4 on XP

ROSt52 gravatar imageROSt52 ( 2013-06-27 10:00:01 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2013-06-27 12:03:19 +0200

oweng gravatar image

@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"
edit flag offensive delete link more


@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.

ROSt52 gravatar imageROSt52 ( 2013-06-27 14:36:59 +0200 )edit

Question Tools


Asked: 2013-06-27 09:53:53 +0200

Seen: 358 times

Last updated: Jun 27 '13