Removing everything outside brackets in a cell

[justin_n84]

Hi!!

I have a bunch of cells which contain data similar to "[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]0.120 (3.048)". I want to delete everything out side the brackets. Is there an easy way to do this?

Thanks in advance
[/FONT]

 

[jmacleary]

OK sounds like youre going my route with 2 replaces. For the first replace you want to replace anything followed by an open bracket with just an open bracket. This is done by entering *( in the search box and ( in the replace box. For the second search, type )* in the search box and ) in the replace box. I have highlighted the exact text to use in red.

 

[Gerald Higgins]

You sure about that

SORRY ! You're right, I was wrong, ALL THREE suggestions, including mine, remove the brackets.

To leave the brackets in . . .

=MID(A1,FIND("(",A1),FIND(")",A1)-FIND("(",A1)+1)

 

[justin_n84]

Thank you. I achieved what I needed to

 

[Rick Rothstein]

For those interested, here is a simple formula solution...

=-REPLACE(C1,1,FIND("(",C1)-1,"")

 

[Gerald Higgins]

Very good Rick !

It looks like that works because Excel is able to interpret the entry
(3.048)
as a number, not a text string, especially when encouraged to do so by use of the - symbol at the front.

So this approach does not work in a slightly different scenario, such as
[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl63, width: 64"]0.120[3.048]
or
0.120X3.048X[/TD]
[/TR]
</tbody>[/TABLE]

Edit to add - don't know why my post has this box around the last couple of lines - wasn't intended by me and now don't know how to get rid of it

 

[Rick Rothstein]

It looks like that works because Excel is able to interpret the entry
(3.048)
as a number, not a text string, especially when encouraged to do so by use of the - symbol at the front.

One of the ways Excel can display negative numbers is when the number is enclosed in parentheses, so I used the REPLACE function to create that text string... if a numerical text string is involved in a mathematical expression, Excel will convert the text number to a real number in order to complete the mathematical operation. Placing a minus sign in front of a number is the same as multiplying that number by minus one which is a mathematical operation. So, in combination, my formula is multiplying a negative text number by minus one... the negative text number becomes a real negative number in order to complete the operation of multiplying it by minus one... and a minus number times a minus number produces a positive result (the product of the two values being multiplied).