Text Extract

[jonnyp138]

Hi there, I have a data dump in a table format, in one of the columns each cell contains numerous strings, the string can be of varying lengths and can contain different words each time, however there is one constant in every cell which is the event ID and is in the form of something like Id:IIWS_IMServer.ntuzof.2715373

I need a formula to extract that event ID.

An example of one of the full cell contents are as follows:

FAILED TO CREATE INCOMING ASSET INCIDENT:
Asset has not been associated to any Person (No Requester Details)
Description (Full): LOWD12SHP10APP01V - Windows 2003 monitoring is CRITICAL: Please investigate
Id:IIWS_IMServer.nu06v4.2719657
Client address:146.12.34.12


Any ideas how best to achieve this?

 

[Rick Rothstein]

Give this formula a try...

=MID(A1,FIND("Id:",A1),FIND(CHAR(10),A1,FIND("Id:",A1))-FIND("Id:",A1))

 

[jonnyp138]

Thanks that is a lot better than I was getting, only snag is when the ID is on the last line it comes up with an error?

 

[Rick Rothstein]

Thanks that is a lot better than I was getting, only snag is when the ID is on the last line it comes up with an error?

Your example did not suggest the ID could be on the last line. Here is my formula modified to handle that...

=MID(A1,FIND("Id:",A1),FIND(CHAR(10),A1&CHAR(10),FIND("Id:",A1))-FIND("Id:",A1))

 

[jonnyp138]

Perfect - Thank you very much!