Stripping text before a number (alpha or numeric)
 

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

Stripping text before a number (alpha or numeric)
 

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

Stripping text before a number (alpha or numeric)
 

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

Stripping text before a number (alpha or numeric)
 

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

Stripping text before a number (alpha or numeric)
 

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

Stripping text before a number (alpha or numeric)
 

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

ARG!

Stupid users... :(

Stripping text before a number (alpha or numeric)
 

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... :(

Stripping text before a number (alpha or numeric)
 

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

Stripping text before a number (alpha or numeric)
 

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

Stripping text before a number (alpha or numeric)
 

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

Stripping text before a number (alpha or numeric)
 

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