Extract everything to the right in cell when a number is encounter

  • Thread starter Thread starter Matt
  • Start date Start date
M

Matt

Have seen examples similar to what I am asking but my problem is it can be
ANY number. I want to read (from left to right) the contents of a cell and
once I encounter a number, copy the remaining text from that point on.
 
Have seen examples similar to what I am asking but my problem is it can be
ANY number. I want to read (from left to right) the contents of a cell and
once I encounter a number, copy the remaining text from that point on.
Click to expand...

If you want to start with the character AFTER that number, then:

=MID(A1,1+MIN(SEARCH({"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"},
A1&{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"})),255)

If you want to INCLUDE that first number, then:

=MID(A1,MIN(SEARCH({"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"},
A1&{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"})),255)

The ,255 at the end should be at least as long as your maximum string length to
be returned.
--ron
 
Worked perfectly Ron. Many thanks!

Ron Rosenfeld said:
If you want to start with the character AFTER that number, then:

=MID(A1,1+MIN(SEARCH({"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"},
A1&{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"})),255)

If you want to INCLUDE that first number, then:

=MID(A1,MIN(SEARCH({"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"},
A1&{"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"})),255)

The ,255 at the end should be at least as long as your maximum string length to
be returned.
--ron
Click to expand...
 
Back
Top