Right and Left Function for words within a cell

A Rose
A Rose ✭✭✭
edited 11/16/21 in Formulas and Functions

Hi,

Any way to copy the 3rd word from the Right in [Address Column]@row?

I have an Address column, I want to copy the City (3rd word from the end) into another column.

[Address Column] Includes: Street, City, State (2 Values I.e. NY), Zip Code (all in 1 cell).

Thank you!

«1

Answers

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭

    @A Rose Assuming you are working in English, most text functions work left to right. I suppose the definition of word is a group of characters separated by spaces?

    You could parse the entire string, using spaces as delimiters, putting each word into a separate column. Then you could count how many words there are in total, and then go find the third-last word.

    So if the string is of manageable length it is possible.

    I'm curious why you would need to perform that function?

    dm

  • A Rose
    A Rose ✭✭✭

    Hi @Dale Murphy

    I have a Address column, now I want to Extract the City into a sperate column, how's that possible?

    Thank you!

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭

    @A Rose If the structure of that column is very consistent, you should be able to break it apart using Find() and either Left() or Right(). Drop an example into here and I can show you the formula I would use.

    dm

  • A Rose
    A Rose ✭✭✭

    Please show me the formula (according to my edited issue above)

    Thank you!

  • A Rose
    A Rose ✭✭✭

    Hi,

    I Have the below for the last word from the right end, I need the 3rd from the last.

    =IF(ISERROR(FIND(" ", [email protected])), "", RIGHT([email protected], LEN([email protected]) - FIND("*", SUBSTITUTE([email protected], " ", "*", LEN([email protected]) - LEN(SUBSTITUTE([email protected], " ", ""))))))
    

    Thank you!

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭

    I worked left to right:

    To find the first separation: FIND(", ", [email protected])

    Using RIGHT() and that result: =RIGHT([email protected], LEN([email protected]) - [Column2]@row)

    Produces: City, State (2 Values I.e. NY), Zip Code (all in 1 cell). **Now City is at the front, in Column3**

    Another find: FIND(", ", [Column3]@row) **This is the result in Column4**

    Then LEFT(): =LEFT([Column3]@row, [Column4]@row - 1) **I subtract one from the length to drop the comma**

    Produces: City

    With a bit more work you should be able to collapse those into one formula. It should work as long as the comma-space separator is consistent.

    dm

  • A Rose
    A Rose ✭✭✭
    edited 11/17/21

    Hi @Dale Murphy,

    I appreciate that, however I need a formula from the right, for the [Address] Column,

    The part of my addresses that are always consistant are the City Satate and Zip - as below,

    123 Test Drive New York NY 12345

    City is always 2 words

    State is always 2 letters

    Zip is always 5 digit.

    (Reason to count from right is because Street may somtimes include the Unit number, and street name may be 1 or more words)

    Thank you!

  • Dale Murphy
    Dale Murphy ✭✭✭✭✭✭

    @A Rose Ah, so there is no comma as a separator?

    Based on your description above I created the attached - gruesome and highly susceptible to your input data changing. Hope you can translate the formulae into a SmartSheet.

    dm

  • A Rose
    A Rose ✭✭✭
    edited 11/17/21

    Thank you @Dale Murphy,

    Wow! that sounds way to complicated for me to relate, I've done complicated formulas in the past but if this requires so many columns then it's way to much for me...

    @Andrée Starå

    @Paul Newcome

    With your permission (hopefully...), may I ask you for your knowledge on this?

    I need a formula from the right end, of the [Address]@row,

    The part of my addresses that are always consistant are the City Satate and Zip - as below,

    123 Test Drive New York NY 12345

    City is always 2 words

    State is always 2 letters

    Zip is always 5 digit.

    (Reason to count from right is because Street may sometimes include the Unit number, and street name may be 1 or more words)


    Is there 1 simple formula for this?


    Thank you!

  • Leibel Shuchat
    Leibel Shuchat ✭✭✭✭✭

    @A Rose

    Below should work, but as mentioned by others the exact way the data comes in needs to be precise.

    Create a helper column called 'Address Split' It should be a Multi Select dropdown and have the below formula: =SUBSTITUTE([Address Column]@row, " ", CHAR(10))

    Then your formula to pull the city would be:

    =MID(SUBSTITUTE(SUBSTITUTE([Address Column]@row, " ", "~", COUNTM([Address Split]@row) - 4), " ", "|", COUNTM([Address Split]@row) - 3), FIND("~", SUBSTITUTE(SUBSTITUTE([Address Column]@row, " ", "~", COUNTM([Address Split]@row) - 4), " ", "|", COUNTM([Address Split]@row) - 3)) + 1, SUM(FIND("|", SUBSTITUTE(SUBSTITUTE([Address Column]@row, " ", "~", COUNTM([Address Split]@row) - 4), " ", "|", COUNTM([Address Split]@row) - 3)) - FIND("~", SUBSTITUTE(SUBSTITUTE([Address Column]@row, " ", "~", COUNTM([Address Split]@row) - 4), " ", "|", COUNTM([Address Split]@row) - 3)) - 1))

    Let me know if that works.

  • A Rose
    A Rose ✭✭✭
    edited 11/19/21

    Hi @Leibel Shuchat,

    Thank you for your input!

    Came back as #Invalid Value.

    Did exactly as you said.

    Thank you!

  • Leibel Shuchat
    Leibel Shuchat ✭✭✭✭✭

    Can you send a screen shot?

  • A Rose
    A Rose ✭✭✭

    It has sensative data, but I have the exact columns and formula you mentioned.

    Thank you!

  • Leibel Shuchat
    Leibel Shuchat ✭✭✭✭✭

    Id the address split a Multi Select column?

  • A Rose
    A Rose ✭✭✭

    No, regular format Text/Number Column

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!