Vlookup() issue - to deal with special character

[chintan_1985]

I have the following naming caonevtion with spcial and without special characters...

Chintan Trivedi
Chintan.Trivedi
Chinta_Trivedi
.Chintan Trivedi

Against which the marks are posted. And I want the marks to be fecthed in different excel sheet. These are the same student and so has same marks. But while I apply VLOOKUP() it fetch all of them insted single entry.

Is there any way to deal with it?

 

[Hui]

Why not clean the data first?
You could simply select the column
Then do a Search/Replace 3 times searching for _, . etc and replace with nothing

 

[chintan_1985]

I can do that.

But want some creative way of doing that by formula or anyother...

the posted special characters are limited, however, the actucal data contains lot more... so I have to identify the all the special characters first and then apply find and replace which comsumes lot of time and its a repetative process also....