I hope I am wording this well....this has been giving me excel rage today
I have a column of cells , with each cell has Street number, street name with direction, a number at the end of the address that represents a count of items at that location. For example "345 Portage Trail avenue west 133"
I want to split into 3 columns using some magical excel formulae
1st cell is the street number. It will always be all data from the left side up until the first space
The 3rd cell is the item count. It will always be all the data from the right side of the cell up until the first space
The 2nd cell is everything else
Using the "345 Portage Trail avenue west 133" example I would like the output to be "345", ""Portage Trail avenue west", and "133" each in their own cell
I performed a text to columns and split off the street number, the remaining cell contains "Portage Trail avenue west 133"
Challenge is that each cell in my column of addresses can have a variable length and number of spaces
For example: "123 Main St 3", "345 East Middle Road 452", "5 Main ridge 22"
I have 2 ways of doing this but there must be an easier way
Way #1 - assume "Portage Trail avenue west 133" is in cell c6
=RIGHT(C6,LEN(C6)-FIND(CHAR(1),SUBSTITUTE(C6," ",CHAR(1),LEN(C6)-LEN(SUBSTITUTE(C6," ",""))))) will give me 133
=LEFT(C6,LEN(C6)-1-LEN((RIGHT(C6,LEN(C6)-FIND(CHAR(1),SUBSTITUTE(C6," ",CHAR(1),LEN(C6)-LEN(SUBSTITUTE(C6," ","")))))))) will give me Portage Trail avenue west
Way #2 - assume "Portage Trail avenue west 133" is in cell c6
=TRIM(RIGHT(SUBSTITUTE(C6," ",REPT(" ",LEN(C6))),LEN(C6))) will give me 133
=LEFT(C6,LEN(C6)-1-LEN(TRIM(RIGHT(SUBSTITUTE(C6," ",REPT(" ",LEN(C6))),LEN(C6))))) will give me Portage Trail avenue west
Any suggestions to simplify this?
Is there a "show all data to the right of the rightmost space" and a "show all data to the left of the rightmost space" formulae??
Bookmarks