Ask Your Question
0

identification of the position of a certain character in text string in a calc cell [closed]

asked 2013-03-18 10:36:17 +0200

ROSt52 gravatar image

I have text strings like

[Your product features] How do you rate in Japan?

in a calc cell and need to identify the position of " ] " to be able to seperate the text in "[ ]" from the rest of the text in the cell.

The text length in and out of "[ ]" varies from cell to cell.

So far I could not identify a function which provides me the result I need.

I thankful for any hint!

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-10-27 01:40:38.537044

Comments

Do you want replace text between this brackets with any value? Try macro from chapters "6.23. Searching a Calc document" and " 7.14.3. Complicated Search And Replace" from http://www.pitonyak.org/AndrewMacro.sxw

JohnSUN gravatar imageJohnSUN ( 2013-03-18 11:04:42 +0200 )edit

3 Answers

Sort by » oldest newest most voted
1

answered 2013-03-18 13:01:24 +0200

JohnSUN gravatar image

How about using Data - Text to Columns TextByBrackets

Of course, you can try the formulas as:

=IF(ISERROR(FIND("]";A1));"";MID(A1;2;FIND("]";A1)-2))
=IF(ISERROR(FIND("]";A1));A1;RIGHT(A1;LEN(A1)-FIND("]";A1)))

But I think this approach is not a good solution

edit flag offensive delete link more
0

answered 2013-03-18 12:33:46 +0200

ROSt52 gravatar image

@JohnSUN - Thanks for link. What a great document is this!!!! However, I could not identify a macro I can use.

I need to identify the position of the character " ] " . Once I know the position I can use combinations of LEFT, MID, RIGHT function to separate the text. At the end I need to have the text within " [ ] " in one cell and the text after the " ] " in another cell.

edit flag offensive delete link more
0

answered 2013-03-18 13:16:07 +0200

ROSt52 gravatar image

@JohnSUN - Thanks for the idea with the CSV file. This looks to me like a smart idea.

I found the solution to which I was very close before posting this question.

I looked not into Calc Help but into https://help.libreoffice.org/Calc/Text_Functions#FIND The example there gave me the idea, that when I omit the position, the function returns the position I am looking for.

Maybe you can add (or trigger adding of) a comment in https://help.libreoffice.org/Calc/Text_Functions#FIND That the FIND returns the position of the search string if the position is omitted, or if the position is used FIND returns TRUE/FALSE.

edit flag offensive delete link more

Comments

"...the idea with the CSV file"? Oh, you're probably talking about the first line in the Help? Choose File - Open - select under "File type": "Text CSV" I was referring the next line - "Choose Data - Text to Columns (Calc)" Simply select column with the necessary data and choose menu Data...

JohnSUN gravatar imageJohnSUN ( 2013-03-19 08:42:00 +0200 )edit

@JohnSUN - no, I am talking about additional information to the FIND function

ROSt52 gravatar imageROSt52 ( 2013-03-19 12:16:11 +0200 )edit

Question Tools

Stats

Asked: 2013-03-18 10:36:17 +0200

Seen: 2,802 times

Last updated: Mar 18 '13