How to copy part of a text into a Smartsheet ?

Options

@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.

Best Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Answer ✓
    Options

    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
    David Tutwiler Overachievers Alumni
    Answer ✓
    Options

    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))))

«1

Answers

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    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
    Options

    [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
    Options

    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
    David Tutwiler Overachievers Alumni
    Options

    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
    David Tutwiler Overachievers Alumni
    Answer ✓
    Options

    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
    David Tutwiler Overachievers Alumni
    Options

    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
    Options

    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
    Options

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

  • Keith Gemmell
    Options

    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
    Options

    =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
    David Tutwiler Overachievers Alumni
    Options

    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
    Options

    Ok, ill give it a go, very exciting.

  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options
  • Keith Gemmell
    Options

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


  • David Tutwiler
    David Tutwiler Overachievers Alumni
    Options

    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!