Extracting part of text with a formula

System

This discussion was created from comments split from: Returning text using IF Function.

Katrina Walker

@Christa Brown we are trying something similar to return a string after finding a set word but could not get it to work so would you please share how you got it to work with MID rather than RIGHT please.

=IF(CONTAINS("Data Collection", [Action Comments History]@row), RIGHT([Action Comments History]@row, LEN([Action Comments History]@row) - FIND("[", [Action Comments History]@row + 1), ""))

Trying to get the string of words after the words Data Collection up until the next [.

Thank you!

Genevieve P.

Hi @Katrina Walker

Can you post the format of the Text String you're looking in to?

For example, if your text string looks like this:

Data Collection [this that] and more

And you're looking to find the text that's only in-between [these], then try this:

=IF(CONTAINS("Data Collection", [Action Comments History]@row), MID([Action Comments History]@row, FIND("[", [Action Comments History]@row) + 1, FIND("]", [Action Comments History]@row) - FIND("[", [Action Comments History]@row) - 1))

Let me know if this makes sense!

Cheers,

Genevieve

Katrina Walker

Hi Genevieve, apologies for the delay in responding and thank you so much for your assistance. I am just trying this out now and will let you know how it goes as think it is what I need. If not then i will post an excerpt of the data for you.

Thanks

Katrina

Katrina Walker

@Genevieve P. Hi again Genevieve. I nearly got this formula to work but I had to change the delimiter to find the correct string.

To explain a little more, I am bringing in a whole screen of text from another system that is updated throughout the life of an incident and I want to pull the last data collection comments. The system that the report comes from always adds in [Date & Email Address] for each comment added. I was trying to use your formula to find data collection then next open [ and work back the way to the end of data collection but I think as there are too many [ ] it only find part of the string.

When I changed it and added a * before data collection I got the correct string. So in the example below I get back 16 different note versions - Approx. 1-2 weeks for DC activity. which is exactly what I want

Cell contents

*Data Collection: 16 different note versions - Approx. 1-2 weeks for DC activity.

[JUL-21-21 rrxxxxx@email.com] 21st July - Incident Raised by GCTM - GCTM to reach out to LTM to understand requirements ***On Hold Pre Planning***

*Data Collection: 16 different note versions - Approx. 4-5 weeks for DC activity.

[JUL-21-21 rrxxxxx@email.com] 21st July - Incident Raised by GCTM - GCTM to reach out to LTM to understand requirements ***On Hold Pre Planning***

*Data Collection: 16 different note versions - Approx. 8-9 weeks for DC activity.

[JUL-21-21 rrxxxx@email.com] 21st July - Incident Raised by GCTM - GCTM to reach out to LTM to understand requirements ***On Hold Pre Planning*

But if I try to use the following (which is the way that it does import to the cell) I cannot get it to work

Cell contents

[JUL-21-21 rrxxxxx@email.com] Data Collection: 16 different note versions - Approx. 1-2 weeks for DC activity.

[JUL-21-21 rrxxxxx@email.com] 21st July - Incident Raised by GCTM - GCTM to reach out to LTM to understand requirements ***On Hold Pre Planning***

[JUL-21-21 rrxxxxx@email.com] Data Collection: 16 different note versions - Approx. 4-5 weeks for DC activity.

[JUL-21-21 rrxxxxx@email.com] 21st July - Incident Raised by GCTM - GCTM to reach out to LTM to understand requirements ***On Hold Pre Planning***

[JUL-21-21 rrxxxxx@email.com] Data Collection: 16 different note versions - Approx. 8-9 weeks for DC activity.

[JUL-21-21 rrxxxx@email.com] 21st July - Incident Raised by GCTM - GCTM to reach out to LTM to understand requirements ***On Hold Pre Planning*

Thanks again for your help!

Katrina

Keith Gemmell

@Genevieve P. @Katrina

Hello Katrina, did you manage to get this work as i am looking to do something exactly the same?

Looking for both your assistance if you don't mind?

Many thanks in advance