My guess is you are looking for this...

=TRIM(MID(LEFT(A1,SEARCH("stock",A1)-1),FIND(":",A1)+1,99))

where I am assuming your text will never be longer than 99 characters... if
it will, just change the 99 to the maximum number of characters there can
be.

--
Rick (MVP - Excel)


"Ken" wrote in message
...
I have the following in cell: A1
"Item : ZB/B/15-2154 - ALL GOLD INDIAN TOMATOES DICED 12x410G Stock On
Hand : 6"

how do I combine the following 2 formulas into 1 formula

1) =RIGHT(A1,LEN(A4)-SEARCH(":",A1))
2) =LEFT(A1,SEARCH("stock",A1)-1)