Stripping text before a number (alpha or numeric)

[data_mattress]

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

 

[Peo Sjoblom]

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

 

[Zack Barresse]

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

 

[Guest]

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

 

[Peo Sjoblom]

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


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[data_mattress]

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

ARG!

Stupid users...

 

[Zack Barresse]

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.

 

[CLR]

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

 

[CLR]

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

 

[CLR]

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

 

[CLR]

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

Vaya con Dios,
Chuck, CABGx3