Right and Left Function for words within a cell

A RoseA Rose ✭✭
edited 11/16/21 in Formulas and Functions
11/16/21 Edited 11/16/21
Answered - Pending Review

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!

Previous1

Answers

  • Dale MurphyDale 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 RoseA 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 MurphyDale 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 RoseA Rose ✭✭

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

    Thank you!

  • A RoseA 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 MurphyDale 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 RoseA 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 MurphyDale 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 RoseA 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 ShuchatLeibel 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 RoseA 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 ShuchatLeibel Shuchat ✭✭✭✭

    Can you send a screen shot?

  • A RoseA Rose ✭✭

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

    Thank you!

  • Leibel ShuchatLeibel Shuchat ✭✭✭✭

    Id the address split a Multi Select column?

  • A RoseA Rose ✭✭

    No, regular format Text/Number Column

Sign In or Register to comment.