software

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},A1amp;quot;0123456789quot; )-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 OneAny 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

nwexcelsolutions.com

quot;data_mattressquot; gt; wrote in message oups.com...
gt;I have a column that's supposed to be an address, but somehow got the
gt; company name mixed in at the beginning:
gt;
gt; 123 Main Street
gt; One Park Place
gt; Acme Company, Inc. 456 Easy Street
gt; Alpha-Omega Dry Cleaning Four-Twenty Highway One
gt;
gt; I can use the formula
gt; =RIGHT(A1,(LEN(A1)-((MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1amp;quot;0123456789quot; )-1)))))
gt; for stripping the text before an actual number
gt;
gt; Acme Company, Inc. 456 Easy Street
gt; to
gt; 456 Easy Street
gt;
gt; BUT - I'm not sure how to strip before a text representation of a
gt; number (one, two, three, etc)
gt;
gt; Alpha-Omega Dry Cleaning Four-Twenty Highway One
gt; to
gt; Four-Twenty Highway One
gt;
gt;
gt; Any suggestions???
gt;
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 = quot;Not Foundquot;
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. lt;ggt;

HTH

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAMquot;data_mattressquot; gt; wrote in message oups.com...
gt;I have a column that's supposed to be an address, but somehow got the
gt; company name mixed in at the beginning:
gt;
gt; 123 Main Street
gt; One Park Place
gt; Acme Company, Inc. 456 Easy Street
gt; Alpha-Omega Dry Cleaning Four-Twenty Highway One
gt;
gt; I can use the formula
gt; =RIGHT(A1,(LEN(A1)-((MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1amp;quot;0123456789quot; )-1)))))
gt; for stripping the text before an actual number
gt;
gt; Acme Company, Inc. 456 Easy Street
gt; to
gt; 456 Easy Street
gt;
gt; BUT - I'm not sure how to strip before a text representation of a
gt; number (one, two, three, etc)
gt;
gt; Alpha-Omega Dry Cleaning Four-Twenty Highway One
gt; to
gt; Four-Twenty Highway One
gt;
gt;
gt; Any suggestions???
gt;
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
quot;data_mattressquot; wrote:

gt; I have a column that's supposed to be an address, but somehow got the
gt; company name mixed in at the beginning:
gt;
gt; 123 Main Street
gt; One Park Place
gt; Acme Company, Inc. 456 Easy Street
gt; Alpha-Omega Dry Cleaning Four-Twenty Highway One
gt;
gt; I can use the formula
gt; =RIGHT(A1,(LEN(A1)-((MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1amp;quot;0123456789quot; )-1)))))
gt; for stripping the text before an actual number
gt;
gt; Acme Company, Inc. 456 Easy Street
gt; to
gt; 456 Easy Street
gt;
gt; BUT - I'm not sure how to strip before a text representation of a
gt; number (one, two, three, etc)
gt;
gt; Alpha-Omega Dry Cleaning Four-Twenty Highway One
gt; to
gt; Four-Twenty Highway One
gt;
gt;
gt; Any suggestions???
gt;
gt;

Wouldn't that cause a problem if the company name is quot;Capital One Holdingquot;
quot;One Hour Photoquot; etc?--

Regards,

Peo Sjoblom

nwexcelsolutions.com
quot;CLRquot; gt; wrote in message
...
gt; It's laborious, but you could separate out those offending rows, and then
gt; write a macro to go through them and REPLACE words one, two, etc, with
gt; 1,2,etc......this would at least give you a beginning point for the
gt; separations, then you could go back and replace the numbers with text
gt; later
gt; if you wish..........
gt;
gt; hth
gt; Vaya con Dios,
gt; Chuck, CABGx3
gt;
gt;
gt;
gt; quot;data_mattressquot; wrote:
gt;
gt;gt; I have a column that's supposed to be an address, but somehow got the
gt;gt; company name mixed in at the beginning:
gt;gt;
gt;gt; 123 Main Street
gt;gt; One Park Place
gt;gt; Acme Company, Inc. 456 Easy Street
gt;gt; Alpha-Omega Dry Cleaning Four-Twenty Highway One
gt;gt;
gt;gt; I can use the formula
gt;gt; =RIGHT(A1,(LEN(A1)-((MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1amp;quot;0123456789quot; )-1)))))
gt;gt; for stripping the text before an actual number
gt;gt;
gt;gt; Acme Company, Inc. 456 Easy Street
gt;gt; to
gt;gt; 456 Easy Street
gt;gt;
gt;gt; BUT - I'm not sure how to strip before a text representation of a
gt;gt; number (one, two, three, etc)
gt;gt;
gt;gt; Alpha-Omega Dry Cleaning Four-Twenty Highway One
gt;gt; to
gt;gt; Four-Twenty Highway One
gt;gt;
gt;gt;
gt;gt; Any suggestions???
gt;gt;
gt;gt;
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 NOSPAMquot;data_mattressquot; gt; wrote in message ups.com...
gt; You have NO idea what a mess this database is in!
gt;
gt; ARG!
gt;
gt; Stupid users...
gt;
True, but this mess is going to take quot;somequot; manipulation......depending on
how many shake out with TEXT numbers the OP might can just boogie them out
by hand during the quot;find and replacequot; operation.....

Vaya con Dios,
Chuck, CABGx3quot;Peo Sjoblomquot; gt; wrote in message
...
gt; Wouldn't that cause a problem if the company name is quot;Capital One
Holdingquot;
gt; quot;One Hour Photoquot; etc?
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt;
gt; quot;CLRquot; gt; wrote in message
gt; ...
gt; gt; It's laborious, but you could separate out those offending rows, and
then
gt; gt; write a macro to go through them and REPLACE words one, two, etc, with
gt; gt; 1,2,etc......this would at least give you a beginning point for the
gt; gt; separations, then you could go back and replace the numbers with text
gt; gt; later
gt; gt; if you wish..........
gt; gt;
gt; gt; hth
gt; gt; Vaya con Dios,
gt; gt; Chuck, CABGx3
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;data_mattressquot; wrote:
gt; gt;
gt; gt;gt; I have a column that's supposed to be an address, but somehow got the
gt; gt;gt; company name mixed in at the beginning:
gt; gt;gt;
gt; gt;gt; 123 Main Street
gt; gt;gt; One Park Place
gt; gt;gt; Acme Company, Inc. 456 Easy Street
gt; gt;gt; Alpha-Omega Dry Cleaning Four-Twenty Highway One
gt; gt;gt;
gt; gt;gt; I can use the formula
gt; gt;gt;
=RIGHT(A1,(LEN(A1)-((MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1amp;quot;0123456789quot; )-1))))
)
gt; gt;gt; for stripping the text before an actual number
gt; gt;gt;
gt; gt;gt; Acme Company, Inc. 456 Easy Street
gt; gt;gt; to
gt; gt;gt; 456 Easy Street
gt; gt;gt;
gt; gt;gt; BUT - I'm not sure how to strip before a text representation of a
gt; gt;gt; number (one, two, three, etc)
gt; gt;gt;
gt; gt;gt; Alpha-Omega Dry Cleaning Four-Twenty Highway One
gt; gt;gt; to
gt; gt;gt; Four-Twenty Highway One
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; Any suggestions???
gt; gt;gt;
gt; gt;gt;
gt;
gt;
True, but this mess is going to take quot;somequot; manipulation......depending on
how many shake out with TEXT numbers the OP might can just boogie them out
by hand during the quot;find and replacequot; operation.....

Vaya con Dios,
Chuck, CABGx3quot;Peo Sjoblomquot; gt; wrote in message
...
gt; Wouldn't that cause a problem if the company name is quot;Capital One
Holdingquot;
gt; quot;One Hour Photoquot; etc?
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt;
gt; quot;CLRquot; gt; wrote in message
gt; ...
gt; gt; It's laborious, but you could separate out those offending rows, and
then
gt; gt; write a macro to go through them and REPLACE words one, two, etc, with
gt; gt; 1,2,etc......this would at least give you a beginning point for the
gt; gt; separations, then you could go back and replace the numbers with text
gt; gt; later
gt; gt; if you wish..........
gt; gt;
gt; gt; hth
gt; gt; Vaya con Dios,
gt; gt; Chuck, CABGx3
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;data_mattressquot; wrote:
gt; gt;
gt; gt;gt; I have a column that's supposed to be an address, but somehow got the
gt; gt;gt; company name mixed in at the beginning:
gt; gt;gt;
gt; gt;gt; 123 Main Street
gt; gt;gt; One Park Place
gt; gt;gt; Acme Company, Inc. 456 Easy Street
gt; gt;gt; Alpha-Omega Dry Cleaning Four-Twenty Highway One
gt; gt;gt;
gt; gt;gt; I can use the formula
gt; gt;gt;
=RIGHT(A1,(LEN(A1)-((MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1amp;quot;0123456789quot; )-1))))
)
gt; gt;gt; for stripping the text before an actual number
gt; gt;gt;
gt; gt;gt; Acme Company, Inc. 456 Easy Street
gt; gt;gt; to
gt; gt;gt; 456 Easy Street
gt; gt;gt;
gt; gt;gt; BUT - I'm not sure how to strip before a text representation of a
gt; gt;gt; number (one, two, three, etc)
gt; gt;gt;
gt; gt;gt; Alpha-Omega Dry Cleaning Four-Twenty Highway One
gt; gt;gt; to
gt; gt;gt; Four-Twenty Highway One
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; Any suggestions???
gt; gt;gt;
gt; gt;gt;
gt;
gt;True, but this mess is going to take quot;somequot; manipulation......depending on
how many shake out with TEXT numbers the OP might can just boogie them out
by hand during the quot;find and replacequot; operation.....

Vaya con Dios,
Chuck, CABGx3quot;Peo Sjoblomquot; gt; wrote in message
...
gt; Wouldn't that cause a problem if the company name is quot;Capital One
Holdingquot;
gt; quot;One Hour Photoquot; etc?
gt;
gt;
gt; --
gt;
gt; Regards,
gt;
gt; Peo Sjoblom
gt;
gt; nwexcelsolutions.com
gt;
gt;
gt;
gt; quot;CLRquot; gt; wrote in message
gt; ...
gt; gt; It's laborious, but you could separate out those offending rows, and
then
gt; gt; write a macro to go through them and REPLACE words one, two, etc, with
gt; gt; 1,2,etc......this would at least give you a beginning point for the
gt; gt; separations, then you could go back and replace the numbers with text
gt; gt; later
gt; gt; if you wish..........
gt; gt;
gt; gt; hth
gt; gt; Vaya con Dios,
gt; gt; Chuck, CABGx3
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;data_mattressquot; wrote:
gt; gt;
gt; gt;gt; I have a column that's supposed to be an address, but somehow got the
gt; gt;gt; company name mixed in at the beginning:
gt; gt;gt;
gt; gt;gt; 123 Main Street
gt; gt;gt; One Park Place
gt; gt;gt; Acme Company, Inc. 456 Easy Street
gt; gt;gt; Alpha-Omega Dry Cleaning Four-Twenty Highway One
gt; gt;gt;
gt; gt;gt; I can use the formula
gt; gt;gt;
=RIGHT(A1,(LEN(A1)-((MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1amp;quot;0123456789quot; )-1))))
)
gt; gt;gt; for stripping the text before an actual number
gt; gt;gt;
gt; gt;gt; Acme Company, Inc. 456 Easy Street
gt; gt;gt; to
gt; gt;gt; 456 Easy Street
gt; gt;gt;
gt; gt;gt; BUT - I'm not sure how to strip before a text representation of a
gt; gt;gt; number (one, two, three, etc)
gt; gt;gt;
gt; gt;gt; Alpha-Omega Dry Cleaning Four-Twenty Highway One
gt; gt;gt; to
gt; gt;gt; Four-Twenty Highway One
gt; gt;gt;
gt; gt;gt;
gt; gt;gt; Any suggestions???
gt; gt;gt;
gt; gt;gt;
gt;
gt;