formatting cell or using functions to remove text from cell A into cell B

[lad]

Hi,

Apologies if this has been asked before, I've done a bit of searching using common words but am not quite sure how to simply describe what I need to do to search effectively.. anyway... here goes...

I receive a spreadsheet with a number of cells in it that look like the following:

25% (5)
0% (0)
30% (14)

What I want to do strip away everything from the cell except the number within the (). I looked at functions but because the string could be of variable length I couldn't work out how to do it, especially as both the % number and the number within the () could vary in length.

Ideally a way of formatting the cell to only show the value within the () would be best without having to copy this info but if it is easy is there a way to extract the value in the () from one cell and paste it into another using some sort of =(function) etc method?

Can anybody help or point me in the right direction please?

Cheers,

J

[Bob Phillips]

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

[MDN111]

Hello !

Suppose your value "25% (5)" or something like that, is in cell A1, try this in cell B1:

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

The formula requires that both "(" and ")" are present in the cell A1.

Grtz,
MDN111.

[lad]

Hi,

Many thanks for the replies. That worked spot on!!

Just to trouble you a little more, if I wanted to do the same for a range of cells, what would be the best way to go about it?

Would it be possible to write some sort of VBA macro that worked based on the range of cells highlighted before execution?

Basically the info in these cells are in blocks, say A1 to D6 and what I want to do is remove the extra detail, leave the values in the () as mentioned and then total the rows and columns.

The no. of rows and columns remain fixed so what I was going to do is say on Sheet2 set up =SUM formulas for each total I want (row and column) and then from the block of cells in Sheet1 use the great little formula you have provided to copy each cell across in the 'new' format, but if there is a way I could do that for a selection of cells rather than just 1 cell that would be awesome?

Does that make any sense?

Cheers,

J

[Bob Phillips]

[vba]

Public Sub Test()
Dim cell As Range
Dim istart As Long

For Each cell In Selection
istart = InStr(cell.Value, "(")
If istart > 0 Then
cell.Value = Mid$(cell.Value, istart + 1, Len(cell.Value) - istart - 1)
End If
Next cell
End Sub
[/vba]

[MDN111]

Hello !

Interesting stuff. I noticed a slight difference between the xld-formula and the MDN11-formula. If you take a very close look you find out that the xld-formula will show only the value between "()" but only under the condition that the last ")" is also the last character in the A1-value. Suppose the cell A1 contains some characters after the last ")", like "25% (5) abc", then the MDN111-formula will continue to do the job. Remember that in his first message lad wanted to "strip away everything from the cell except the number within the () ". I just posted this message to prevent lad from having problems should, one day, his A1-values have some characters after ")".
Grtz,
MDN111.

[Cyberdude]

Here's a technique you might like to try:
[vba]Sub Extract()
Dim ary
Const XXX As String = "ABC (123) GHJ"

ary = Split(XXX, "(")
ary = Split(ary(1), ")")

MsgBox ary(0)
End Sub[/vba]
The "Split" function can be handy at times.

[lad]

Hi all,

Thanks for these... I'll hopefully be able to make some time later today to play around and see what happens.

Also thanks MDN111, the cells will always end with a ")" but kind of you to point that out and also explain how the formulas work.

Cheers.