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 !!
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 !!