How can I remove numbers from alphanumeric cell in excell?

Question

If I have a column in excel that has a list of words followed by numbers in parenthesis, is there a formula or way to create a macro to remove all numbers and parenthesis and just leave the words?

FYI: I'm using MS Office 2007

Answer 1

Data > Text to Column > Delimited > Other.  Then add an open paren in the provided box.  It works.  I just did it.

-MM

Answer 2

Say A1 is one of the cell that has the full name followed by a number, Try this array formula

=SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( A1,1*MID( A1,MATCH( TRUE,ISNUMBER( 1*MID(A1,ROW($1:$100 ),1)), 0),COUNT( 1*MID(A1,ROW($1:$100 ),1))),"") ,"(","") ,")","")

If you copy the above formula to a cell, while the cell is selected hit F2 then hold down Shift and ctrl then hit enter, it will activate the formula to array formula, on the formula bar, curly brackets { } will enclosed it.

{=SUBSTITUTE( SUBSTITUTE( SUBSTITUTE( A1,1*MID( A1,MATCH( TRUE,ISNUMBER( 1*MID(A1,ROW($1:$100 ),1)), 0),COUNT( 1*MID(A1,ROW($1:$100 ),1))),"") ,"(","") ,")","")}

Note: the $100 in above formula is the max. length of the text in A1, in normal case, it would be adequate

Please email for more details