Formula returns #Value!

[mlt1]

A7 = name and address as "Mister Big5555East Road
C7 = Number of characters before first number

=Min(search({0,1,2,3,4,5,6,7,8,9},A7$"0123456789")),left(A7,C7-1),Right(A7,Len(A7)-c7+1))

All works when I use three cells with each step in the formula.

Thank you!

 

[RoryA]

It's not clear to me what you are actually trying to do, or where you want the formula? Do you just want everything after the first number? If so:

=MID(A7,Min(search({0,1,2,3,4,5,6,7,8,9},A7&"0123456789")),len(A7))

should do.

 

[Fluff]

Maybe in (for instance D7)

=TEXTBEFORE(A7,SEQUENCE(,10,0))

and then

=SUBSTITUTE(A7,D7,"")

 

[Scott Huish]

I think you can do all of that with one formula:
=SUBSTITUTE(A1,TEXTBEFORE(A1,SEQUENCE(,10,0)),"")

 

[mlt1]

I think you can do all of that with one formula:
=SUBSTITUTE(A1,TEXTBEFORE(A1,SEQUENCE(,10,0)),"")

Thank you!!