Not returning correct values in all cells

Hi,
If someone could look at this immediately please, donno if its a silly mistake
in sheet "HMC_Facility_wise_PR_PO.." i have taken data from row L to combine with d to have a combination shown in E
Now in sheet "raw prpo report" i want to find this data in row A and if found that match should write in sheet "HMC_Facility_wise_PR_PO.." in column F else return with "Not found" but something strange some values not matching
one example is row 28 in sheet"HMC_Facility_wise_PR _PO.." value 1691662-01 is there in sheet "raw prpo report" but still in column f showing as not found whereas it should display value(1691662-01), could you throw light please


Thanks
mp

A2=SUBSTITUTE(SUBSTITUTE(B2&"-"&TEXT(I2,"00"),"*",""),"‬","")
Try this in "Raw PRPO report" Sheet
and copy towards down - there are some special characters are there in column A

Your problem is that there are some non printing characters in these cells. They need to be removed. They evaluate to Char 63 - which corresponds to a ? mark. Clearly that isn't present in these cells. I'm nowhere near a PC right now and can't intuitively reach a solution. Others with PC access should be able to fix this.

I tried clean and (I think) substitute for all the NP characters and it didn't fix it for me. If I removed the first and last characters of each string, using a combination of -- LEFT and MID, i started ti get somewhere; but i had to leave my PC.

Your data, especially column D of the sheet PO received looks like it has been subjected to much experimentation by yourself, making a messy situation even messier. Can you go back to your source data and repost a MUCH smaller virgin data set. Remove unnecessay sheets.

In that colun, you have a random mixture of real numbers, text that looks like a number and text bracketed by non-printing characters. I guarantee that a virgin set of data will have only one of these (the last one) and reaching a workable solution will be much easier.

Your data, especially column D of the sheet PO received looks like it has been subjected to much experimentation by yourself, making a messy situation even messier. Can you go back to your source data and repost a MUCH smaller virgin data set. Remove unnecessay sheets.

In that colun, you have a random mixture of real numbers, text that looks like a number and text bracketed by non-printing characters. I guarantee that a virgin set of data will have only one of these (the last one) and reaching a workable solution will be much easier.

It's night time here. I'm not at home; but relaxing in a remote part of Goa.

I know (I think - 99%) what the problem is, and how to fix it. However, with non-virgin data, the solution will have to be unnnecessarily complicated.

I'll have a go in a.m.; but a virgin dataset will make YOUR life and MINE much easier.