Remove special characters from cells.

[uday]

Hi,

Need to remove random special characters from A column by formula and also lookup only by consisting special character from A column to D column. Please find the attached file.

Thanks

 

[Villalobos]

Hi,

Check this file.
The question 3 is not enough clear for me, could you please describe it?

 

[uday]

Thanks..

I want a lookup formula which will lookup only numbers match from A column.
In A column data are merged with special characters. Look up values will be D and table array will be A. Hope this is clear now.

 

[uday]

I will be very obliged, if you explain the formula which you are using to remove all those special characters from A column.

I want to learn it from it.

 

[Nebu]

Hi:
Here is a non-array solution.

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$10),1))*ROW($1:$10),0),ROW($1:$10))+1,1)*10^ROW($1:$10)/10)

The ROW($1:$10) is the length of the string, if you are working with strings longer than 10 simply change the 10 in ROW($1:$10) to what ever length you prefer.

Thanks

 

[uday]

Great to have Excel in system and chance to get working with beautiful formula.

Thank you guys for help.