Removing partial text from a cell

Jo Petch

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!

Bassam Khalil

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

Bassam Khalil

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

Jo Petch

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

@Jo Petch

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

Bassam Khalil

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

Jo Petch

Thanks so much :)

David Noël

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

Jo Petch

Change the LEFT for RIGHT in the formula

David Noël

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

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

@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

just perfect and makes complete sense. thank you!

Paul DG

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

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

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