I have a column that's supposed to be an address, but somehow got the
company name mixed in at the beginning:

123 Main Street
One Park Place
Acme Company, Inc. 456 Easy Street
Alpha-Omega Dry Cleaning Four-Twenty Highway One

I can use the formula
=RIGHT(A1,(LEN(A1)-((MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789" )-1)))))
for stripping the text before an actual number

Acme Company, Inc. 456 Easy Street
to
456 Easy Street

BUT - I'm not sure how to strip before a text representation of a
number (one, two, three, etc)

Alpha-Omega Dry Cleaning Four-Twenty Highway One
to
Four-Twenty Highway One


Any suggestions???

I doubt it would be possible unless you can find something else that is
unique given that possible spelled address numbers could be so many, I mean
take


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com




"data_mattress" wrote in message
oups.com...
I have a column that's supposed to be an address, but somehow got the
company name mixed in at the beginning:

123 Main Street
One Park Place
Acme Company, Inc. 456 Easy Street
Alpha-Omega Dry Cleaning Four-Twenty Highway One

I can use the formula
=RIGHT(A1,(LEN(A1)-((MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789" )-1)))))
for stripping the text before an actual number

Acme Company, Inc. 456 Easy Street
to
456 Easy Street

BUT - I'm not sure how to strip before a text representation of a
number (one, two, three, etc)

Alpha-Omega Dry Cleaning Four-Twenty Highway One
to
Four-Twenty Highway One


Any suggestions???

If you only go by where the numbers start, you could use a UDF like this ..

Public Function ReturnAddress(ByVal strText As Variant) As String
Dim n As Long, strChar As String
For n = 1 To Len(strText)
strChar = Mid(strText, n, 1)
If IsNumeric(strChar) Then Exit For
Next
If n = Len(strText) Then ReturnAddress = "Not Found"
ReturnAddress = Right(strText, Len(strText) - n + 1)
End Function

You could probably do it with formulas, but I'm probably too lazy to come up
with such a large formula, and I'm faster with VBA. <g

HTH

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"data_mattress" wrote in message
oups.com...
I have a column that's supposed to be an address, but somehow got the
company name mixed in at the beginning:

123 Main Street
One Park Place
Acme Company, Inc. 456 Easy Street
Alpha-Omega Dry Cleaning Four-Twenty Highway One

I can use the formula
=RIGHT(A1,(LEN(A1)-((MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789" )-1)))))
for stripping the text before an actual number

Acme Company, Inc. 456 Easy Street
to
456 Easy Street

BUT - I'm not sure how to strip before a text representation of a
number (one, two, three, etc)

Alpha-Omega Dry Cleaning Four-Twenty Highway One
to
Four-Twenty Highway One


Any suggestions???

It's laborious, but you could separate out those offending rows, and then
write a macro to go through them and REPLACE words one, two, etc, with
1,2,etc......this would at least give you a beginning point for the
separations, then you could go back and replace the numbers with text later
if you wish..........

hth
Vaya con Dios,
Chuck, CABGx3



"data_mattress" wrote:

I have a column that's supposed to be an address, but somehow got the
company name mixed in at the beginning:

123 Main Street
One Park Place
Acme Company, Inc. 456 Easy Street
Alpha-Omega Dry Cleaning Four-Twenty Highway One

I can use the formula
=RIGHT(A1,(LEN(A1)-((MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789" )-1)))))
for stripping the text before an actual number

Acme Company, Inc. 456 Easy Street
to
456 Easy Street

BUT - I'm not sure how to strip before a text representation of a
number (one, two, three, etc)

Alpha-Omega Dry Cleaning Four-Twenty Highway One
to
Four-Twenty Highway One


Any suggestions???

Wouldn't that cause a problem if the company name is "Capital One Holding"
"One Hour Photo" etc?


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"CLR" wrote in message
...
It's laborious, but you could separate out those offending rows, and then
write a macro to go through them and REPLACE words one, two, etc, with
1,2,etc......this would at least give you a beginning point for the
separations, then you could go back and replace the numbers with text
later
if you wish..........

hth
Vaya con Dios,
Chuck, CABGx3



"data_mattress" wrote:

I have a column that's supposed to be an address, but somehow got the
company name mixed in at the beginning:

123 Main Street
One Park Place
Acme Company, Inc. 456 Easy Street
Alpha-Omega Dry Cleaning Four-Twenty Highway One

I can use the formula
=RIGHT(A1,(LEN(A1)-((MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789" )-1)))))
for stripping the text before an actual number

Acme Company, Inc. 456 Easy Street
to
456 Easy Street

BUT - I'm not sure how to strip before a text representation of a
number (one, two, three, etc)

Alpha-Omega Dry Cleaning Four-Twenty Highway One
to
Four-Twenty Highway One


Any suggestions???

You have NO idea what a mess this database is in!

ARG!

Stupid users... :(

Well, if you had a list of the addresses seperated, you could perform a
match of them with the MATCH function set to look at the part (of the
length) desired.

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"data_mattress" wrote in message
ups.com...
You have NO idea what a mess this database is in!

ARG!

Stupid users... :(

True, but this mess is going to take "some" manipulation......depending on
how many shake out with TEXT numbers the OP might can just boogie them out
by hand during the "find and replace" operation.....

Vaya con Dios,
Chuck, CABGx3


"Peo Sjoblom" wrote in message
...
Wouldn't that cause a problem if the company name is "Capital One
Holding"
"One Hour Photo" etc?


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"CLR" wrote in message
...
It's laborious, but you could separate out those offending rows, and
then
write a macro to go through them and REPLACE words one, two, etc, with
1,2,etc......this would at least give you a beginning point for the
separations, then you could go back and replace the numbers with text
later
if you wish..........

hth
Vaya con Dios,
Chuck, CABGx3



"data_mattress" wrote:

I have a column that's supposed to be an address, but somehow got the
company name mixed in at the beginning:

123 Main Street
One Park Place
Acme Company, Inc. 456 Easy Street
Alpha-Omega Dry Cleaning Four-Twenty Highway One

I can use the formula

=RIGHT(A1,(LEN(A1)-((MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789" )-1))))
)
for stripping the text before an actual number

Acme Company, Inc. 456 Easy Street
to
456 Easy Street

BUT - I'm not sure how to strip before a text representation of a
number (one, two, three, etc)

Alpha-Omega Dry Cleaning Four-Twenty Highway One
to
Four-Twenty Highway One


Any suggestions???

True, but this mess is going to take "some" manipulation......depending on
how many shake out with TEXT numbers the OP might can just boogie them out
by hand during the "find and replace" operation.....

Vaya con Dios,
Chuck, CABGx3


"Peo Sjoblom" wrote in message
...
Wouldn't that cause a problem if the company name is "Capital One
Holding"
"One Hour Photo" etc?


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"CLR" wrote in message
...
It's laborious, but you could separate out those offending rows, and
then
write a macro to go through them and REPLACE words one, two, etc, with
1,2,etc......this would at least give you a beginning point for the
separations, then you could go back and replace the numbers with text
later
if you wish..........

hth
Vaya con Dios,
Chuck, CABGx3



"data_mattress" wrote:

I have a column that's supposed to be an address, but somehow got the
company name mixed in at the beginning:

123 Main Street
One Park Place
Acme Company, Inc. 456 Easy Street
Alpha-Omega Dry Cleaning Four-Twenty Highway One

I can use the formula

=RIGHT(A1,(LEN(A1)-((MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789" )-1))))
)
for stripping the text before an actual number

Acme Company, Inc. 456 Easy Street
to
456 Easy Street

BUT - I'm not sure how to strip before a text representation of a
number (one, two, three, etc)

Alpha-Omega Dry Cleaning Four-Twenty Highway One
to
Four-Twenty Highway One


Any suggestions???

True, but this mess is going to take "some" manipulation......depending on
how many shake out with TEXT numbers the OP might can just boogie them out
by hand during the "find and replace" operation.....

Vaya con Dios,
Chuck, CABGx3


"Peo Sjoblom" wrote in message
...
Wouldn't that cause a problem if the company name is "Capital One
Holding"
"One Hour Photo" etc?


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"CLR" wrote in message
...
It's laborious, but you could separate out those offending rows, and
then
write a macro to go through them and REPLACE words one, two, etc, with
1,2,etc......this would at least give you a beginning point for the
separations, then you could go back and replace the numbers with text
later
if you wish..........

hth
Vaya con Dios,
Chuck, CABGx3



"data_mattress" wrote:

I have a column that's supposed to be an address, but somehow got the
company name mixed in at the beginning:

123 Main Street
One Park Place
Acme Company, Inc. 456 Easy Street
Alpha-Omega Dry Cleaning Four-Twenty Highway One

I can use the formula

=RIGHT(A1,(LEN(A1)-((MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789" )-1))))
)
for stripping the text before an actual number

Acme Company, Inc. 456 Easy Street
to
456 Easy Street

BUT - I'm not sure how to strip before a text representation of a
number (one, two, three, etc)

Alpha-Omega Dry Cleaning Four-Twenty Highway One
to
Four-Twenty Highway One


Any suggestions???

Sorry for the repeats.....my OE5 is acting up.....

Vaya con Dios,
Chuck, CABGx3


"CLR" wrote in message
...
True, but this mess is going to take "some" manipulation......depending on
how many shake out with TEXT numbers the OP might can just boogie them out
by hand during the "find and replace" operation.....

Vaya con Dios,
Chuck, CABGx3


"Peo Sjoblom" wrote in message
...
Wouldn't that cause a problem if the company name is "Capital One
Holding"
"One Hour Photo" etc?


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com



"CLR" wrote in message
...
It's laborious, but you could separate out those offending rows, and
then
write a macro to go through them and REPLACE words one, two, etc, with
1,2,etc......this would at least give you a beginning point for the
separations, then you could go back and replace the numbers with text
later
if you wish..........

hth
Vaya con Dios,
Chuck, CABGx3



"data_mattress" wrote:

I have a column that's supposed to be an address, but somehow got the
company name mixed in at the beginning:

123 Main Street
One Park Place
Acme Company, Inc. 456 Easy Street
Alpha-Omega Dry Cleaning Four-Twenty Highway One

I can use the formula


=RIGHT(A1,(LEN(A1)-((MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789" )-1))))
)
for stripping the text before an actual number

Acme Company, Inc. 456 Easy Street
to
456 Easy Street

BUT - I'm not sure how to strip before a text representation of a
number (one, two, three, etc)

Alpha-Omega Dry Cleaning Four-Twenty Highway One
to
Four-Twenty Highway One


Any suggestions???