Text Extract

jonnyp138

Board Regular
Joined
May 2, 2015
Messages
50
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?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Give this formula a try...

=MID(A1,FIND("Id:",A1),FIND(CHAR(10),A1,FIND("Id:",A1))-FIND("Id:",A1))
 
Upvote 0
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?
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top