Right and Left Function for words within a cell

A Rose · 11/16/21

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!

Dale Murphy · 11/16/21

@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 · 11/16/21

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 · 11/16/21

@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 · 11/16/21

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

Thank you!

A Rose · 11/16/21

Hi,

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

=IF(ISERROR(FIND(" ", Address@row)), "", RIGHT(Address@row, LEN(Address@row) - FIND("*", SUBSTITUTE(Address@row, " ", "*", LEN(Address@row) - LEN(SUBSTITUTE(Address@row, " ", ""))))))

Thank you!

Dale Murphy · 11/16/21

I worked left to right:

To find the first separation: FIND(", ", Address@row)

Using RIGHT() and that result: =RIGHT(Address@row, LEN(Address@row) - [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 · 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 · 11/17/21

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

Cityname Finder.xlsx

dm

A Rose · 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 S · 11/19/21

@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 · 11/19/21

Hi @Leibel Shuchat,

Thank you for your input!

Came back as #Invalid Value.

Did exactly as you said.

Thank you!

Leibel S · 11/19/21

Can you send a screen shot?

A Rose · 11/19/21

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

Thank you!

Leibel S · 11/19/21

Id the address split a Multi Select column?

A Rose · 11/19/21

No, regular format Text/Number Column

Right and Left Function for words within a cell

Answers

Help Article Resources

Categories