Extract text between colon and word

[Dave Chiskey]

Dear Team,

I've looked a few different variations and threads to try and solve the below using SUBSTITUTE, MID, SEARCH in various combinations but I am not getting what I need.
I have the following date in A1:

PROACTIVE: SEV 1
INCIDENT START TIME: 01/17/2021 14:25:04
ID: urgent-abc12345-6789101f011
ALT ID: ABC-12345-000-F011
PRODUCT: NETWORK
EQUIPMENT TYPE: NETWORK DEVICE
INCIDENT TYPE: NETWORK ISSUE ABC-12345-000-F011: Network Loss SOURCED FROM INTERNAL MONITORING SYSTEM
MONITORING ALARM ID: ABCDE-1234BND56RMRMWRTE
TEXT: Device is inaccessible for monitoring

Expected result of formula: extract the incident type wording. For example in the above I would only want to extract " Network Loss"
The constant would be "SOURCED FROM INTERNAL MONITORING SYSTEM" and the location of the colons.
Ideally I need to extract wording after the 2nd colon and before the word Sourced, the word/s will be variable lengths depending on the incident type bein reported.

I need this in a formula not VBA if possible.

Thank you kindly in advance

Dave

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Dave Chiskey]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

thank you moderator, I have updated version and platform now.

 

[Fluff]

Thanks for that, how about

+Fluff 1.xlsm
	A	B
1	PROACTIVE: SEV 1 INCIDENT START TIME: 01/17/2021 14:25:04 ID: urgent-abc12345-6789101f011 ALT ID: ABC-12345-000-F011 PRODUCT: NETWORK EQUIPMENT TYPE: NETWORK DEVICE INCIDENT TYPE: NETWORK ISSUE ABC-12345-000-F011: Network Loss SOURCED FROM INTERNAL MONITORING SYSTEM MONITORING ALARM ID: ABCDE-1234BND56RMRMWRTE TEXT: Device is inaccessible for monitoring	Network Loss
Lists

Cell Formulas
Range	Formula
B1	B1	=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,SEARCH("Sourced from internal",A1)-1),":",REPT(" ",100)),100))

 

[Dave Chiskey]

Thanks for that, how about

+Fluff 1.xlsm
	A	B
1	PROACTIVE: SEV 1 INCIDENT START TIME: 01/17/2021 14:25:04 ID: urgent-abc12345-6789101f011 ALT ID: ABC-12345-000-F011 PRODUCT: NETWORK EQUIPMENT TYPE: NETWORK DEVICE INCIDENT TYPE: NETWORK ISSUE ABC-12345-000-F011: Network Loss SOURCED FROM INTERNAL MONITORING SYSTEM MONITORING ALARM ID: ABCDE-1234BND56RMRMWRTE TEXT: Device is inaccessible for monitoring	Network Loss
Lists

Cell Formulas
Range	Formula
B1	B1	=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,SEARCH("Sourced from internal",A1)-1),":",REPT(" ",100)),100))

Thank you Fluff this is awesome thank you VERY much!

 

[Fluff]

You're welcome & thanks for the feedback.