MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Remove some data


Posted by Jack in UK on January 02, 2002 10:41 AM

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
PS The braces is always afeter the name ie to the right if that helps


Cheers guys.


Posted by Aladin Akyurek on January 02, 2002 10:52 AM

Hi Jack,

Lets get it a bit more clear:

MrExcel (564) must become just MrExcel

Right?

Aladin

=======

Posted by Jack in UK on January 02, 2002 10:58 AM

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!

Posted by Aladin Akyurek on January 02, 2002 11:12 AM

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).

Posted by Jack in UK on January 02, 2002 1:22 PM

: 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

Posted by Aladin Akyurek on January 02, 2002 2:00 PM

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


Posted by Jack in UK on January 02, 2002 2:08 PM


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

Posted by Ivan F Moala on January 02, 2002 8:57 PM

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

Posted by Aron on January 07, 2002 7:43 PM

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