Extract text between colon and word

Dave Chiskey

New Member
Joined
Apr 7, 2014
Messages
42
Office Version
  1. 2013
Platform
  1. Windows
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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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’)
 
Upvote 0
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.
 
Upvote 0
Thanks for that, how about
+Fluff 1.xlsm
AB
1PROACTIVE: 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 monitoringNetwork Loss
Lists
Cell Formulas
RangeFormula
B1B1=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,SEARCH("Sourced from internal",A1)-1),":",REPT(" ",100)),100))
 
Upvote 0
Solution
Thanks for that, how about
+Fluff 1.xlsm
AB
1PROACTIVE: 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 monitoringNetwork Loss
Lists
Cell Formulas
RangeFormula
B1B1=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,SEARCH("Sourced from internal",A1)-1),":",REPT(" ",100)),100))
Thank you Fluff this is awesome thank you VERY much!
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

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