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