Rmove Name [SOLVED]

[Nipendra.dhiman]

Hi Experts,


can we remove the specify name in cell value if I have to remove Nipendra here then?

only by excel formula I wan to do it.


raj,Npendra,kamal,raju

Sam,Harish,Nipendra,Raj

Praveen,Amar,Amit,Nipendra

Kiran,Aman,Pari,Nipendra,Mukesh,Arjoo


Regards

Nipen

 

[Smallman]

Hi Nipen


Just use Find and Replace. Highlight the Column your data is in Ctrl F - criteria ,Nipendra


Replace - just leave this blank


That should fix your problem.


Take care


Smallman

 

[Hui]

Or you can use the Substitute Function

=SUBSTITUTE(A2,"Nipendra","")


Both this and Smallman's solution will leave double ,'s in the text


Which can be fixed with

=SUBSTITUTE(SUBSTITUTE(A2,"Nipendra",""),",,",",")

 

[Smallman]

Hi Hui


The comma before Nipendra in my solution will mean there are no double commas in the text. So it should work for every permutation EXCEPT where the first word is Nipendra. In this case neither of our methods currently work.


A slight tweek though and you are there.


Take care


Smallman

 

[Nipendra.dhiman]

thanks a lot guys you amazing....


Nipen

 

[Smallman]

Hui


This addition should make your formula work for a scenario where Nipendra is the first work in the string.


=IF(LEFT(A2,9)="Nipendra,",SUBSTITUTE(A2,"Nipendra,",""),SUBSTITUTE(SUBSTITUTE(A2,"Nipendra",""),",,",","))


I had not considered this in my original post but I had thought the post through enough to consider the double comma ,, problem as shown in my original.


Take care


Smallman