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