Parseing to produce text only

[Guest]

I am looking for a formula which will parse a cell containing both numbers
and then text.
eg cell contains 1:44. 5/1.(36.44) The text begins here....
The resulting formula will produce-The text begins here....
There is no common delimiter

regards Mick
You don't need a parachute to skydive,
You only need a parachute to skydive twice...

 

[T. Valko]

eg cell contains 1:44. 5/1.(36.44) The text begins here....

There is no common delimiter

What about the closing ")" ?

As a last resort this array formula** will work if there truly is no common
delimiter *and* the text starts with a letter from a to z:

=MID(A1,MATCH(1,(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1))>=65)*(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1))<=90),0),255)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[T. Valko]

You can use a shorter formula** by using a defined name.

Insert>Name>Define
Name: Letters
Refers to:

={"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z"}

Then, this array formula** :

=MID(A1,MIN(FIND(Letters,UPPER(A1)&Letters)),255)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Guest]

Thanks Mr Biff,
Works a treat!
I hope Excel 4 macros understands array formulas.

regards Mick
The ark was built by amateurs, the Titanic was built
by professionals.

 

[Harlan Grove]

....
As a last resort this array formula** will work if there truly is no
common delimiter *and* the text starts with a letter from a to z:

=MID(A1,MATCH(1,(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1))>=65)
*(CODE(MID(UPPER(A1),ROW(INDIRECT("1:"&LEN(A1))),1))<=90),0),255)

....

Picky: this begins at the first letter in the string. That may be what the
OP wants, but the OP's request is ambiguous. Text might begin with the first
letter after the last numeral. And it becomes much harder if the numeric
part could contain letters and the text part embedded numbers.

 

[T. Valko]

You're welcome. Thanks for the feedback!