Solved: Changing Arrangement of Text in a cell

[Poundland]

Guys,

I wonder if you can assist me;

I have a range of cells that have text formatted in the following way;

Ancillary (900)BABY (300)BATTERIES (100)CELEBRATIONS (550)CLOTHING (700)DIY (600)ENTERTAINMENT (150)FOOD & DRINK (850)GARDENING (450)HEALTH & BEAUTY (200)HOMEWARES (400)HOUSEHOLD (350)PET (650)SEASONAL/EVENTS (480)STATIONERY (750)TOYS (800)XMAS (500)

I need to change the arrangement of the cells so that the Brackets and numbers appear in the cell before the text, how can I achieve that?

Poor example but each cell will only contain one of the above variants, not all of the variants.

Any help is most welcome and appreciated.

[georgiboy]

If your text was in cell "B1" then maybe something like this formula...

=LEFT(B1,FIND(")",B1))

or for a range of cells using vba (this removes the brackets)...

Sub Split_String()
Dim ary As Variant
Dim rCell As Range
Dim MyRange As Range

Set MyRange = Range("A1:A100") '<-- Change to suit

On Error Resume Next
For Each rCell In MyRange.Cells
ary = Split(Replace(rCell.Value, "(", ""), ")")
rCell.Resize(, UBound(ary) + 1).Value = ary
Next rCell

End Sub

Hope this helps

[mdmackillop]

=MID(B2,FIND("(",B2,1),1000)&LEFT(B2,FIND("(",B2,1)-1)

[Poundland]

Thanks for your help guys, both worked a treat.