• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Searching in a cell from right to left???

Im_Offset

Member
Hello to all of you super smart excel people! I hope to be one of you someday....

I know that I can use the "find" function to locate the fist instance of something in a cell. Is it possible to be able to find the last instance of something in a cell, or to find the first instance of something in a cell searching the cell right to left instead of left to right?

Here is what I am trying to do:
I have a list of home addresses. These addresses contain three parts to them: A home number, a street name, and a street type. If the addresses are listed in column A, I would like to list the home number in column B, the street name in column C, and the street type in Column D. The issue I am having is that street names can be up to 4 words in length (the home number and street type are only one "word" in length).

Finding the home number is easy enough using the "left" and "find" functions. I find the first space in the cell and then use the "left" function to return all the characters before that space. But identifying the street type is proving challenging because of the variable number of words in a street name.

Is there a way that I can search a cell to find the first instance of a character searching the cell right to left instead of left to right?

If that is possible, then I could find the last instance of a space (or the first space from the right side of the cell) and then use the "right" function to return all the characters after that space. Then the remaining characters in the original cell would be the street name, and I could use "mid" or "substitute" to return the street name.

Thank you for your time in helping me find a solution to my problem. One day I will learn to solve such problems on my own. Until then...

Im_Offset !! :)
 

GraH - Guido

Well-Known Member
Hi, as always these kind of threads are easier replied to if you'd at least upload a sample data set that is representative for your use case.
I've said it before, and I will repeat many more times :), these transformations are most likely a walk in the park with Power Query (get & transform).
Or even, ... in later versions, flash fill is amazing for this, able to detect by examples you type in the pattern you are after. Often even beating what one can do with PQ.
see this reference https://ozdusoleil.com/2019/03/05/excels-flash-fill-vs-power-querys-column-by-example-8/

Tried some of my vintage skills
If the address would be in D3
=MID(D3,FIND("|",SUBSTITUTE(D3," ","|",LEN(D3)-LEN(SUBSTITUTE(D3," ",""))))+1;255)
 
Last edited:

Fluff13

Member
Another option.
With the address in A1
In B1 use =LEFT(A1,FIND(" ",A1)-1)
In C1 use =TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1,""),D1,""))
In D1 use =TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100))
 
Top