Unwrap the Text and Find Serial Number

[nkms143]

Hi,

I'm Working on a project in which i need to download the data from Web in an excel sheet (Table 1) and tag the serial number from Table 1 to another Sheet (Manual Worksheet). However the data in Table in Comes as Wrapped text and any Lookup or Index Match Formula is not working and gives me error message as #N/A. At present i'm unwrapping the text manually from (Table 1) and applying lookup formula. Hence, i request is there any solution or formula so that the text (Column Name "Unit ID") in Downloaded Data (Table 1) Worksheet should be unwrapped Automatically. Because more than 10,000 records are needed to unwrap every month.
i'm hereiwth attaching a sample workbook for your reference. Formula will be very helpful. Thanks in advance.

 

[Luke M]

For table 1, formula in C3 to clean up the text is:
=CLEAN(B3)

Then, in Manual Sheet, formula would be like:
=INDEX('Table 1'!$A$3:$A$1035,MATCH(A2,'Table 1'!$C$3:$C$1035,0))

 

[nkms143]

Thank you sir....