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

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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