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!

 

[Rick Rothstein]

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%

You didn't define the problem very well? I'll say! I don't think anyone would ever have figured out this is what you wanted based on your other posts. Okay, anyway, see if this macro works for you...

[table="width: 500"]
[tr]
	[td]Sub SplitNumbersAndPercents()
  Columns("A").Replace ")", "", xlPart, , , , False, False
  Columns("A").TextToColumns Range("B1"), xlDelimited, , , False, False, False, False, True, "("
End Sub[/td]
[/tr]
[/table]

 

[Gregfox]

Many thanks Rick, It worked, however it changed all the numbers in the entire column, I just wanted to take from one cell and put the results into two other cells .
If I change Columns("A") to let's say C10 would it move the numbers to let's say J1, and K1.
I'm not great at VBA and I don't understand the code, but I’m trying to understand it.
Again Thank you.