Some expert Excel/Open Office help?

Factual Questions
1

I have a column of text strings. Some but not all of the cells have comments contained within parentheses to the right of the string, but within the same cell.

I need a formula that will copy the the string up to but not including the first open parenthesis “(”, and also not including a blank space if it immediately precedes the parenthesis (which one does in most cases).

The formula can either be written to equal that string, or to place the string in some other adjacent column.

I mentioned Open Office because I’m using it at home, but if you give me an Excel example it will probably be close enough to work with a little fiddling. There’s nearly 10.000 rows, so doing this manually would take an enormous amount of time.

2

Assuming your string is in d22 and less than 5000 characters (and that I understood the question) (and that I didn’t get ninja’d while fooling with it):

=LEFT(D22,MIN(IFERROR(FIND(" (",D22)-1,5000),IFERROR(FIND("(",D22)-1,5000),LEN(D22)))

3

Well, it didn’t work by just pasting it in and changing the cell reference, but that would be too good to be true anyway. I gonna assume there’s some syntax differences to make it work on OE. This is an excellent start, and I thank you.

4

Not sure if Open Office has the text to columns feature, but if it does could you use that? Use the ( as the delimter and you should end up with everything to the left of it in its own column. You could then use trim to get rid of the trailing space if necessary I think.

5

Keep in mind that OpenOffice uses semicolon (:wink: to separate arguments in a function call, whereas Excel uses a comma (,).

6

Arg! Semicolon in parens - not smiley! D’Oh!

7

This has some paren issues, I think. There aren’t complete pairs (there are two more open parens than close parens). Before I look at figuring out where they would logically go, I’m gonna look at Blunt’s idea of seeing if I can get it to load into the spreadsheet from the source file in way that solves the problem.

8

Ok, I’ve solved the problem taking a little but of this and a little bit of that.

I imported the column into a spreadsheet, where I searched for “<space>(”, and replaced these with just a paren then I just copied the column out into Notepad.

Then I copied it back into an empty spreadsheet using the paren as a column delimiter, and Whoo hoo it works. Thanks!

9

Well, it’s done, but here’s how you could have done it just in open/libreoffice:

Select the column you want
Edit…Replace " (" with “(” (get rid of leading spaces)
Tools…Text to Column
Use “(” as a delimiter
You’ll now have a seperate column with the comments and the closing “)”
Edit…Replace “)” with “” (just to clean it up)

All done!

10

And, for completeness, if you’d wanted to do this to a text file, say mydata.txt, outside of *office then:

sed ‘s/\s*(.*//’ mydata.txt > mydatawithoutcomments.txt