Need a function

[Guest]

I created a database that has a column for Addresses. In
order to search for items all on the same street I listed
the street number behind the street name. i.e. Main St.
2106 and now I need to create another column that places
the street number back in front of the street name. i.e.
2106 Main St. I know there is a function that will do
this for me but I cannot seem to figure it out. Help!!!

 

[Jason Morin]

One way:

=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(INDIRECT("1:"&LEN
(A1))),1)),0),255)&" "&LEFT(A1,MATCH(TRUE,ISNUMBER(1*MID
(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)-2)

Array-entered (meaning press ctrl-shift-enter).

HTH
Jason
Atlanta, GA

 

[Stephen Bye]

You would have made life a lot easier for yourself if you had just designed
your database with the street number in a separate column.

 

[Ron Rosenfeld]

I created a database that has a column for Addresses. In
order to search for items all on the same street I listed
the street number behind the street name. i.e. Main St.
2106 and now I need to create another column that places
the street number back in front of the street name. i.e.
2106 Main St. I know there is a function that will do
this for me but I cannot seem to figure it out. Help!!!

Assuming there is a number at the end of every entry, then:

=RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1," ",
"~",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))& " " &
LEFT(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-
LEN(SUBSTITUTE(A1," ",""))))-1)


--ron

 

[Harlan Grove]

One way:

=MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW(INDIRECT("1:"&LEN
(A1))),1)),0),255)&" "&LEFT(A1,MATCH(TRUE,ISNUMBER(1*MID
(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)-2)

Array-entered (meaning press ctrl-shift-enter).

A1: 107th Street 123
Formula: #VALUE!

A1: West 57th Street 987
Formula: 57th Street 987 West

You're searching left to right. You should be searching right to left.

=RIGHT(A1,MATCH(TRUE,ISERROR(-MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,
1)),0)-1)&" "&LEFT(A1,LEN(A1)-MATCH(TRUE,ISERROR(-MID(A1,LEN(A1)-
ROW(INDIRECT("1:"&LEN(A1)))+1,1)),0))