Removing partial text from a cell

Options

Hi

I have a column of text as the examples below where I need to remove the dates at the end. The dates are all different - I just need to remove the text from the "/" onwards. Any ideas?

374 GF A1 to A4 Evap Flometer Pipe / 18 Feb 2021

377 No. 4 Thick Juice tank / 10 Feb 2021

Thanks for reading!

Best Answer

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 05/20/21 Answer ✓
    Options

    Hi @Jo Petch

    Hope you are fine, please use the following formula

    =IF(ISBLANK([Original Text]@row), "", LEFT([Original Text]@row, (FIND("/", [Original Text]@row) - 1)))

    the following screenshot shows the result


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 05/20/21 Answer ✓
    Options

    Hi @Jo Petch

    Hope you are fine, please use the following formula

    =IF(ISBLANK([Original Text]@row), "", LEFT([Original Text]@row, (FIND("/", [Original Text]@row) - 1)))

    the following screenshot shows the result


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Jo Petch
    Jo Petch ✭✭✭
    Options

    Hi

    I'm very well thank you, I hope you are good too.

    Thank you so much for the above, that worked perfectly! Would you mind just explaining which part did what?

    Best wishes

    Jo

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 05/20/21
    Options

    @Jo Petch

    You are welcome and I will be happy to help you any time.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    @Jo Petch

    For your question about which part did what?

    the main concept in my formula is to Find the number of characters before the mark "/" and use this number to truncate the text before this mark "/".

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Jo Petch
    Jo Petch ✭✭✭
    Options

    Thanks so much :)

  • David Noël
    David Noël ✭✭✭✭
    Options

    What if you wanted the opposite? All data after the / ?

  • Jo Petch
    Jo Petch ✭✭✭
    Options

    Change the LEFT for RIGHT in the formula

  • David Noël
    David Noël ✭✭✭✭
    edited 12/23/22
    Options

    Tried that didn't exactly work. In a column we have an email address. I want the helper column with the formula to only display the URL. (e.g., test@abc.com - show just abc.com). So, only display all data after the @ sign.

  • David Noël
    David Noël ✭✭✭✭
    Options

    checking back in on the questions above. would you know of a formula that would remove all date before the @ sign in an email address? so if a column had david@abc.com, the new column with the formula would pull from the email column and only display, in my example, abc.com?

  • Toufong Vang
    Toufong Vang ✭✭✭✭✭
    Options

    @David Noël, you can use the MID() function to return everything after the @ character.

    Assuming there will be only one email address in the column, EmailAddress, and that the domain name (the part after the "@") is no more than 100 characters, then your formula would be:

    = MID( EmailAddress@row, FIND("@", EmailAddress@row) + 1, 100)
    

    https://help.smartsheet.com/function/mid

  • David Noël
    David Noël ✭✭✭✭
    Options

    just perfect and makes complete sense. thank you!

  • Paul DG
    Paul DG ✭✭✭
    edited 08/25/23
    Options

    @Toufong Vang or @Bassam Khalil How would this formula be edited to also find and remove the last name if the column has either a full name or email address? I am looking to pull either the first name or email prefix, since either could be in the column. I tried OR formula, but kept getting "invalid argument".

    This formula works for first name, but would also like to add the email aspect as you showed above:

    =IF(NOT(ISBLANK([Reporter info]@row)), LEFT([Reporter info]@row, FIND(" ", [Reporter info]@row) - 1))

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Paul DG

    Try this:

    =IF([Reporter info]@row = "", "", IF(FIND(" ", [Reporter info]@row) > 0, LEFT([Reporter info]@row FIND(" ", [Reporter info]@row) - 1), IF(FIND("@", [Reporter info]@row) > 0, LEFT([Reporter info]@row, FIND("@", [Reporter info]@row) - 1), [Reporter info]@row)))


    I'll break it out for you:

    =IF([Reporter info]@row = "", "",

    If the Reporter Info cell is blank, then return blank

    IF(FIND(" ", [Reporter info]@row) > 0, LEFT([Reporter info]@row FIND(" ", [Reporter info]@row) - 1),

    If the Report info cell is NOT blank, then check if there's a space... if there is, grab everything to the left.

    IF(FIND("@", [Reporter info]@row) > 0, LEFT([Reporter info]@row, FIND("@", [Reporter info]@row) - 1), 

    If the Report info cell is NOT blank AND does not have a space, then check if there's an @ symbol... if there is, grab everything to the left of that Symbol

    [Reporter info]@row)))

    Otherwise, simply return the Reporter Info from this row.


    The last statement is a default in case the value in the cell only has one name (no spaces or @ symbols).

    Let me know if that makes sense and works for you!

    Cheers,

    Genevieve

  • Paul DG
    Paul DG ✭✭✭
    edited 08/29/23
    Options

    @Genevieve P. That worked! Appreciate the thorough explanation, for I love learning for the next time:) Thank you so much!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!