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

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(IND IRECT("1:"&LEN(A1))),1))<=90),0),255)

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

--
Biff
Microsoft Excel MVP


"Mick Barry" wrote in message
...
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...

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

InsertNameDefine
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)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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(IND IRECT("1:"&LEN(A1))),1))<=90),0),255)

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

--
Biff
Microsoft Excel MVP


"Mick Barry" wrote in message
...
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...

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.

"T. Valko" wrote...
eg cell contains 1:44. 5/1.(36.44) The text begins here....
There is no common delimiter
....
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.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Mick Barry" wrote in message
...
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.