Formula needed to extract a number from a cell

Calling all Excel wizards

I have a sheet that contains about 12000 of rows of data which unfortunately is very inconsistent in how it’s presented, it’s a raw data file that I’m sent from an external company and can’t be reformatted. The data contains various employee profile information such as employee numbers, phone log ons etc. In some rows the employee number may be in column A, in another it may be in column D and so on, which is making life very difficult trying to complete lookups etc. Some rows may contain 5 cells worth of data, others may contain up to 20

This is an example of a formula I’ve used to combine the cell data in a row ="I"&F2&"I"&G2&"I"&H2&"I"&I2&"I"&J2&"I"&K2&"I" (Thanks to a previous forum solution by mrice)

The result for example being the following…. I1234567I};{IS01DPAIglobal\1144518I}}III

I’m then using the below formula to check whether an employee number is present in the previous formula result

=IF(COUNTIF(Extract!$C$2:$C$13000,"*I"&C1&"I*")>0,"TRUE","FALSE")

So if C1 contains 1234567, the result will be TRUE

So my sheet contains 12000 results from the first formula, and I have say 8000 employee numbers I need to look up, so I can check which of the 8000 employees are referenced in the list of 12000, however I now need to be able to highlight the other 4000 that don’t contain one of the 8000 employee numbers if that makes sense

I’m thinking I somehow need to extract the employee number from the formula result, and I can then run a conventional lookup. The issue is the employee number isn't always in the same place

I hope that makes some sort of sense, any suggestions would be very welcome

Twaddy

Hi Alkey

Thanks very much for your response and apologies I'm only replying now

As I have approx 8000 IDs I need to check your formula wouldn't quite do what I need, which in hindsight I didn't explain very well. I essentially need the 7 digit number from C2 to populate in D2 for each row

I also posted on an alternative site and had the following suggestion as a UDF which seems to work very well for what I need

Thanks again

Please Login or Register  to view this content.