Need a function

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

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

-----Original Message-----
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!!!
.

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.

wrote in message
...
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!!!

On Thu, 4 Dec 2003 07:59:47 -0800, wrote:

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

"Jason Morin" wrote...
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))

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.