How to copy part of a text into a Smartsheet ?

Keith Gemmell

@David Tutwiler Hi David, i see you are very knowledgeable when i comes to Smartsheet. I not sure you would be willing to help me as i am pretty new to the game and have little experience.

I am trying to pull a sting of text across to Smartsheet. But only part of the text.

For example; from half way down the text to the end.

is this possible?

Many thanks for any guidance that you can offer.

David Tutwiler

I've tested this formula by using the whole text and also just the last line and it appears to work:

=IF(FIND("Data Collection", Description@row) > 0, RIGHT(Description@row, LEN(Description@row) - FIND("Data Collection", Description@row) + 1), IF(FIND("DC", Description@row) > 0, RIGHT(Description@row, LEN(Description@row) - FIND("DC", Description@row) + 1), ""))

David Tutwiler

Gotcha. If you want an end to the part that grabs the text, you will need to provide the position that the function needs to stop. Also, the function RIGHT is not the correct function anymore, as we're trying to find text in the middle of your block of text. Right now we just have the RIGHT function going all the way to the end.

If you are going to use the first bracket after the DC is found (ie. [Jan...]), then you will need to inject another FIND function so you know what position that is in. You'll most likely need to feed it the position of the first DC again so that it knows to start AFTER you've found a DC. I think the formula should look something like this:

(*note, in my test sheet my column is named Description. You will need to replace that row name with the row name on your sheet)

=IF(FIND("Data Collection", Description@row) > 0, MID(Description@row, FIND("Data Collection", Description@row), FIND("[", Description@row, FIND("Data Collection", Description@row)) - FIND("Data Collection", Description@row)), IF(FIND("DC", Description@row) > 0, MID(Description@row, FIND("DC", Description@row), FIND("[", Description@row, FIND("DC", Description@row)) - FIND("DC", Description@row))))

David Tutwiler

This is possible. You can use functions like LEFT and RIGHT and maybe FIND depending on what the problem you're trying to solve is.

Can you provide an example of the text that will be in Smartsheet and then how you would decide which part of the text should be left at the end of the formula?

Keith Gemmell

[FEB-17-22 as250714@ncr.com] 18th Feb - LTM has asked for permission to move the BRM test module to the BOT premise to do the new THB20 note template testing.  Still awaiting for BoT response/approval so LTM has been asked to chase for this.

[FEB-11-22 kg185162@ncr.com] 11th Feb - LTM has asked for permission to move the BRM & GBxx test module to the BOT premise to do the new THB20 note template testing during Feb.21-25, 2022. 

Awaiting for their approval. 

[JAN-25-22 kg185162@ncr.com] 25th Jan - LTM cannot gain access to the BOT office to carry out RT, access has been requested and now waiting a response.  

[DEC-31-21 as250714@ncr.com] 31-Dec- TT THB-HDV300-CS-04_Test01 released. Tentative RT schedule is end January. GCTM to follow up with LTM mid-January to confirm still on track/exact schedule.

[DEC-29-21 lm185173@ncr.com] 29th Dec- TT THB-HDV300-CS-04_Test01 released. Awaiting RT schedule.

FML code updated in GCTT.

[DEC-29-21 lm185173@ncr.com] 29th Dec- TT THB-HDV300-CS-04_Test01 released.

FML code updated in GCTT.

[DEC-29-21 lm185173@ncr.com] 29th Dec- Packaged template has been saved onto server

[DEC-24-21 kg185162@ncr.com] 24th Dec - TT release date is expected to be 28th Dec.

[DEC-17-21 kg185162@ncr.com] 17th Dec - LTM has now approved the quotation and FS report. Glory should now have the Go Letter to commence development.

[DEC-17-21 kg185162@ncr.com] 15th Dec - Awaiting approval of the FS report from the LTM, once approved we will need the Go Letter.

[DEC-15-21 kg185162@ncr.com] 15th Dec - FS report sent to LTM.

[DEC-14-21 kg185162@ncr.com] 14th Dec - Quote an d FS report received. Awaiting for the FS report to be confirm from Glory, this will then be released to LTM. 

[DEC-08-21 lg185078@ncr.com] 8th Dec: FS report ERD is 14th Dec.

[NOV-26-21 kg185162@ncr.com] 26th Nov - awaiting FS report from Glory

[NOV-19-21 kg185162@ncr.com] 19-Nov: new Polymer 20 THB note. Development on the test template to commence work today (19th Nov 2021).

[NOV-12-21 as250714@ncr.com] 12-Nov: new Polymer 20 THB note. Data shared with Glory on Monday 8th Nov for review. Awaiting approval of DC and FS Report/Quote release schedule.

Keith Gemmell

So this is the text that i need to extract from.

I need to take the text from the first time we see 'DC or 'Data Collection'. To the end of the text.

In this case it will be DC to end of Text.

David Tutwiler

Understood. Would each of these be their own row in Smartsheet, or would all of this text be inserted into a single cell?

David Tutwiler

I've tested this formula by using the whole text and also just the last line and it appears to work:

=IF(FIND("Data Collection", Description@row) > 0, RIGHT(Description@row, LEN(Description@row) - FIND("Data Collection", Description@row) + 1), IF(FIND("DC", Description@row) > 0, RIGHT(Description@row, LEN(Description@row) - FIND("DC", Description@row) + 1), ""))

David Tutwiler

Also, as a note, I called the column the text went into in Smartsheet Description. If you name your column something else you will need to replace every instance of Description@row with the column name you used.

Keith Gemmell

Hi David, all into a single cell.

The text is regularly updated in Share Point and when this is updated i need it to update Smartsheet also.

Cheers

Keith Gemmell

So where it sates Column@row i need to add my column name, is that correct?

Keith Gemmell

Sorry, i meant Description@row.

My column is called Data Collection Comments (GCTT) so this will be named Data Collection Comments (GCTT)@row ?

Keith Gemmell

=IF(FIND("Data Collection", Data Collection Comments (GCTT)@row) > 0, RIGHT(Data Collection Comments (GCTT)@row, LEN(Data Collection Comments (GCTT)@row) - FIND("Data Collection", Data Collection Comments (GCTT)@row) + 1), IF(FIND("DC", Data Collection Comments (GCTT)@row) > 0, RIGHT(Data Collection Comments (GCTT)@row, LEN(Data Collection Comments (GCTT)@row) - FIND("DC", Data Collection Comments (GCTT)@row) + 1), ""))

Summary - This is what it should look like? I will try it out now.

Thank you David

David Tutwiler

Almost. Since your column name is more than one word you'll need to surround it in brackets. So something like:

=IF(FIND("Data Collection", [Data Collection Comments (GCTT)]@row) > 0, RIGHT([Data Collection Comments (GCTT)]@row, LEN([Data Collection Comments (GCTT)]@row) - FIND("Data Collection", [Data Collection Comments (GCTT)]@row) + 1), IF(FIND("DC", [Data Collection Comments (GCTT)]@row) > 0, RIGHT([Data Collection Comments (GCTT)]@row, LEN([Data Collection Comments (GCTT)]@row) - FIND("DC", [Data Collection Comments (GCTT)]@row) + 1), ""))

Keith Gemmell

Ok, ill give it a go, very exciting.

David Tutwiler

@Keith Gemmell Did that work for you?

Keith Gemmell

@David Tutwiler it doesn't seem to be picking up the relevant cell that we need to take the text from.

David Tutwiler

It is important to remember that this finds the first instance of the DC mentioned and then grabs all of the text after that. I see in your second example that there are multiple instances of DC. I'm not sure what you want pulled over in that instance.