Finding records in a long string of multiple data

[Ian Sykes]

Hello

I have extracted from outlook the subject title of thousands of e-mails and dropped into excel. I need to match this upto a list of reference numbers to see if they appear in the subject title, however the number could appear at any point in the string (if at all)

Below is an example of the e-mail string(s)

• Bob to ring back for details @ 18:34 JAMES @ 17:14 (LR 17:12) ID 2869703
• ID: 1918501. ISSUED @ 14:11 VM BOB @ 14:02

examples of reference numbers to lookup with the answer (is it in the extract)

• ref number is it in the extract
• 2869703 Y
• 1234567 N
• 1918501 Y

cheers in advance

 

[jayalaxmi]

Hello

See if something like this work for you

=MID(H17,FIND("ID",H17)+3,8)

Regards
Jaya

 

[Ian Sykes]

Hello
only issue with this is, if the ID is followed by a : it goes out of line, in some occurrences there is no word "ID" either.

 

[Nunes]

Hi Ian

Jayalaxmi's formula will return the ID number from a long string, providing it is always in the format "ID XXXXXXXX" within your string.
If that's what you want/need, fine.
If you want to test for the presence of a given string (that would be the 8 digit number ID) within a string (that would be the email subject), you can try the following:

=if(iserror(find(Cell_with_ID,Cell_with_Subject)),"ID not found";"ID found")

Hope it helps

Best
Nuno