Remove ( and all after

[Gregfox]

Hi all, I'm using Excel 2007 and have a cell that looks like this [$100(-1.2%)] and would like to remove the () and all inside so that it looks like this [$100]
Thanks!

 

[JoeMo]

Excel Workbook
	A	B
3	[$100(-1.2%)]	[$100]
Sheet7

 

[jtakw]

Hi,

Another way:

Book1
	A	B
1	[$100(-1.2%)]	[$100]
Sheet108

Cell Formulas
Range	Formula
B1	=LEFT(A1,FIND("(",A1)-1)&"]"

 

[Gregfox]

Many thanks that did it!

 

[Rick Rothstein]

If your ultimate goal is to fix the data within the cells the data is in, then you can do it using Excel's Replace dialog box. Select the column the data is in, press CTRL+H to bring up the Replace dialog box, put (*) in the "Find what" field, leave the "Replace with" field empty, press the "Options>>" button and make sure the "Match entire cell contents" checkbox is not checked, then click the "Replace All" button.

 

[Gregfox]

Opps, It worked in a normal excel spreadsheet but (my bad)I forgot to mention I was using VBA basic, so I could not find a way to make it work in VBA basic.
Any thoughts?

 

[Scott Huish]

Function OnlyDollars(r As String) As String
OnlyDollars = "[$" & Val(Mid(r, 3)) & "]"
End Function

 

[Gregfox]

Thank you, but I'm having trouble getting it to run. When I paste it into a macro I get a compiler error "expected END SUB"

 

[Scott Huish]

It doesn't go in a sub, it's a function. Put it outside your macro. You call it from the sub.

For example:

Function OnlyDollars(r As String) As String
OnlyDollars = "[$" & Val(Mid(r, 3)) & "]"
End Function

Sub Test()
MsgBox OnlyDollars("[$100(-1.2%)]")
End Sub

 

[Gregfox]

Thank you for the help, but I’m still struggling. I did not define the problem very well.
The number in lets say A1 could be 1, 2, up to 8 digits followed by a “(“ with an number and a % sign followed by a “)”
Like this +3,067.06(0.24%) in one cell.
I wish to (using VBA) 1. Separate the number like 3067.06 without the “+” and put it in a cell.
2. Separate the 0.24% without the “(“ or “)” and put that into another cell.

The final result should look like this B1 3067.06 and B2 .024%

Sorry I wasn’t more precise.