Function to extract element of a complex delimited string (Pick D3 database)

[John Vinciguerra \(Chemform\)]

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

 

[Bernie Deitrick]

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)" <[email protected]>
wrote in message
news:[email protected]...

 

[Tom Ogilvy]

If I understand what you are trying to do,

DDE doesn't work with dynamic formulas to the best of my knowledge.

You would have to write code that creates the dde formula string, then
places it in the cell as a hard coded formula.