Remove some data

Hi Jack,

Lets get it a bit more clear:

MrExcel (564) must become just MrExcel

Right?

Aladin

=======

Hi Aladin
Correct but name changes for all rows but number in braces is right if that helps
jack

PS good to see you back, good times i trust!

Lets get it a bit more clear: MrExcel (564) must become just MrExcel Right? Aladin ======= : Hi all, need help here, wonder if anyone can help : If B2:B??? = Mr Excel (564) or any number> 10000 if 10000+ = A0001 to A9999 i need to remove them all the bits in braces??? : rouble is this can be any name Mr A BCDEF etx, ive millins of these to update daily as comes from ODBC download, need custon funtion really or formula or VBA to do just this , so leave name only as Mr A Excel ect : Any ideas. : Jack

Yep. That helps.

Thanks. And yes, loved to be once again in Istanbul.

Now, the formula:

In C2 enter: =TRIM(SUBSTITUTE(A2,RIGHT(A2,SUMPRODUCT((LEN(A2)-LEN(SUBSTITUTE(A2,{"(",")",0,1,2,3,4,5,6,7,8,9},""))))),""))

and give a double click on the little black square of C2 (or just copy down).

Enjoy.

Aladin

PS. You mention that you have lots of entries that you want to clean up. After you've done that, select from C3 to the last entry, copy it and do an Edit|Paste Special -> Values in place (excluding C2 where you keep the formula).

: Lets get it a bit more clear: : MrExcel (564) must become just MrExcel : Right? : Aladin : =======

Aladin, well done works perfect any number but fails on A564 or any letter in the braces???

Any ideas, ive done some 250,000 so far and cross checked but any letter with number returns ( ???
Thanks thats a cool formula

Jack

Jack --

The formula is actually meant for data like

{"aladin1234";
"sax12";
"s2345";
"jack123456"}

in order to remove the numeric part from the end or from anywhere in a string. I think it's probably not needed in you case because it seems you have always a space then left paren in the strings from which you want to remove everything including " (" from the end. If that's the case, the following would be more appropriate and less costly:

=TRIM(LEFT(A2,SEARCH("(",A2)-1))

This one should handle regularly structured strings like

{"MrExcel (123)";
"xsante (A564)";
"aladin (x4321)";
"jack(+z21)"}

and will produce as result:

{"MrExcel";
"xsante";
"aladin";
"jack"}

I hope this solves the problem. If not, let me know.

I'm sorry I gave you that "cool formula" (it figures, no?).

Aladin

Aladin

P E R F E C T I O N !
And yes thats another cool formula, thanks, sa now i have much wok to do, well done and a big thanks again

Take care
Jack

Hi jack
if you do this daily and you have millions todo
Then perhaps it would be best to do this via a macro ??
Just a thought........

Ivan

Why not parse it with a '(' ?

Hi, sorry to but it...couldn't you use 'Data->Text to Columns...' and delimit with a '(' and then delete the second column? Assuming you want all data to the right of the name removed...?

Sorry to be nosey,
Excel Newbie