How to cut the parentheses value from a row of cells?

[jmcarper09]

Hey there,

I was hoping someone could help me find a shortcut for cutting the parentheses value from a row of cells. The cells in my row each have a percentage and confidence interval value in parentheses and I would like to cut out the confidence interval. I have about sixty columns to work with, so I'd like to be able to copy a formula across a row.

Ex: I'd like to take "29% (6%-7%)" and take out the "(6%-7%)" without having to individually cutting each value out.

Any help is much appreciated.

Thanks,
Jane

 

[BenMiller]

To be clear - first highlight that column, and click "Replace All" in the dialog box. This is also assuming you want to remove ALL parentheses from the column.

 

[Rick Rothstein]

jmcarper09,

How about:

Excel Workbook
	A
1	29% (6%-7%)
2	29%
Sheet1

I don't think that formula is correct as written. Try changing the 29% to, say, 9% or 1234%. I think the formula you were after is this one...

=LEFT(A1,FIND("(",A1)-2)&"<"

 

[VoG]

My result

Excel Workbook
	A
1	29% (6%-7%)
2	29.00%
Sheet5

 

[Rick Rothstein]

Ben,

I'm not familiar with FIND/REPLACE-- how would I use that in this scenario?

I would do it differently than Ben has outlined to you. Try it this way. Select the column of cells with your data in it and then call up the Find and Replace dialog box (Ctrl+H will do it)... put %* (percent sign followed by an asterisk) in the "Find what" field and % (percent sign only) in the "Replace with" field... next click the "Options>>" button to reveal all the options for this dialog box and make sure there is no check mark in the checkbox labeled "Match entire cell contents"... finish off by clicking the "Replace All" button.

EDIT NOTE: The procedure changes your original data unlike a formula which is written different cells.

 

[BenMiller]

You're 100% right! I didn't think this one through.

 

[jmcarper09]

Great, thank you both!