John,

You can find ASCII characters using formulas by typing

=FIND("

then holding down the alt key and by using the numeric keypad,
entering, for example 0254

then using a closing " and finishing the formula in the usual manner

So, to extract the left part of string up to the first 0254, you would
use

=LEFT(A1,Find(" enter 0254 using the numeric keypad while holding
down alt ",A1)-1)

HTH,
Bernie
MS Excel MVP

"John Vinciguerra (Chemform)"
wrote in message
u...
Hi Excel Gurus,

I have tried doing a google search for something similar to this but
had no
luck,
I wonder if you guys/gals could help?

Is there a function (or how would I write a user function) to do the
following.

Assuming I have a text string in a cell delimited as such:


PRODUCT1.CODE-PRODUCT1.NAME-PRODUCT1.COST*PRODUCT2.CODE-PRODUCT2.NAME-
PRODUC
T2.PRICE
(1,1) - (1,2) - (1,3) * (2,1) -
(2,2) -
(2,3)

* Represents ASCII Char. 254
- Represents ASCII Char. 253

Is it possible to extract 1 of these string elements into another
cell in a
manner
similar to this:

=EXTRACT(COL,ROW,1,1) to yield PRODUCT1.CODE from the string

=EXTRACT(COL,ROW,2,3) to yield PRODUCT2.PRICE from the string

I have tried using the FIND() and MID() functions but I think I'm on
the
wrong track.

Why do I want to do this? :
I have a Pick/D3 database which uses a 3 dimensional record
structure. It is
accessed by a terminal emulator which provides some pick programs
and excel
modules that allow communication with excel via DDE. The excel
functions
take
the form =pickread("FILENAME","KEY.ID", ATTRIBUTE,VALUE,SUB-VALUE)
in the
string
above PRODUCT1.NAME would be in ATTRIBUTE 1 and VALUE 2 (sub-value
is not
used in
this example).

I have a spreadsheet that does between 600 and 1200 of these
pickread()
functions
which can be very slow if the pick server is under a heavy a load. I
would
like to
be able to do one pickread() and return a large delimited record and
split
it
up in excel. I realise that this may be a slow task, however nowhere
near as
slow
as doing a DDE database read for 1200 cells.

In the ideal world I would like to be able to work with three
delimiters,
ASCII chars
252(Sub-Value Mark), 253(Value Mark) and 254(Attribute Mark) so the
function
above would
look more like =EXTRACT(COL,ROW,1,2,3) where COL,ROW is the cell
that the
large delimited
string is stored in, 1 is the Attribute, 2 is the Value, 3 is the
Sub-Value.


I hope this makes sense.


Any help greatly appreciated.


Regards,

John Vinciguerra