I need to be able to parse my records into seperate columns: Size, Material,
#ofColors, Coating, Finishing, Custom Specs.

The delimiter is ||. I tried using the Data, Text to Columns options,
however if a record does not contain all of the fields, then it ends up in
the wrong column. How can I parse by looking for the Field Name contained
within the record (i.e. Size:, Material:). Here's my sample data. The
first record has 4 fields (size, material, # of colors, finishing). The
second record has 5 fields (size, material, # of colors, coating, finishing).
Thanks.

Size: 3.25" x 12.5", folds up to 8.5" || Material: 10 point C2S || # of
Colors: 4/cp 2/sides plus a varnish seal and metallic scratch off one side.
|| Finishing: Die-cut holes and strip waste. Score at top and perf at bottom
fold. Seal fold closed with a dot of easy remove waste.

Size: 3-1/4" x 8-3/8" || Material: 10 point C1S || # of Colors: 4C/1C
(two-sided) plus overall gloss on front side only || Coating: Gloss on front
side only || Finishing: Die-cut hole, strip waste and score.

Size: 3.25" x 12.5" flat, folds up to 3.25" x 8.5" || Material: 10 pt C1S ||
# of Colors: 4/1 || Finishing: Die-cut and strip waste from hole. Score near
neck and perf where it folds up. Fold and hold in place with a dot of easy
remove glue.

Size: 3.257"w x 8.512"h || Material: 10pt C1S Cover || # of Colors: 4/1 +
Gloss on front side only || Finishing: Die cut hole for neck of bottle and
score

Size: Bottlenecker for the 500 ml Propel bottle. Please confirm the
following will work: 3.75"w x 9.25"h || Material: 10 point C1S || # of
Colors: 4C/0 (one-sided) plus overall gloss on front side only || Custom
Specs: Die cut Would like to use an existing die

Great idea to post sample records! When I read them on this newsgroup,
tho, they contain line breaks and appear over several lines. Does your
data appear as one line per record? (The overall solution depends on
this.)

It's one long field in a cell on the spreadhseet.

"Dave O" wrote:

Great idea to post sample records! When I read them on this newsgroup,
tho, they contain line breaks and appear over several lines. Does your
data appear as one line per record? (The overall solution depends on
this.)

On Mon, 23 Jan 2006 08:32:03 -0800, Gloria Lewis
wrote:

I need to be able to parse my records into seperate columns: Size, Material,
#ofColors, Coating, Finishing, Custom Specs.

The delimiter is ||. I tried using the Data, Text to Columns options,
however if a record does not contain all of the fields, then it ends up in
the wrong column. How can I parse by looking for the Field Name contained
within the record (i.e. Size:, Material:). Here's my sample data. The
first record has 4 fields (size, material, # of colors, finishing). The
second record has 5 fields (size, material, # of colors, coating, finishing).
Thanks.

Size: 3.25" x 12.5", folds up to 8.5" || Material: 10 point C2S || # of
Colors: 4/cp 2/sides plus a varnish seal and metallic scratch off one side.
|| Finishing: Die-cut holes and strip waste. Score at top and perf at bottom
fold. Seal fold closed with a dot of easy remove waste.

Size: 3-1/4" x 8-3/8" || Material: 10 point C1S || # of Colors: 4C/1C
(two-sided) plus overall gloss on front side only || Coating: Gloss on front
side only || Finishing: Die-cut hole, strip waste and score.

Size: 3.25" x 12.5" flat, folds up to 3.25" x 8.5" || Material: 10 pt C1S ||
# of Colors: 4/1 || Finishing: Die-cut and strip waste from hole. Score near
neck and perf where it folds up. Fold and hold in place with a dot of easy
remove glue.

Size: 3.257"w x 8.512"h || Material: 10pt C1S Cover || # of Colors: 4/1 +
Gloss on front side only || Finishing: Die cut hole for neck of bottle and
score

Size: Bottlenecker for the 500 ml Propel bottle. Please confirm the
following will work: 3.75"w x 9.25"h || Material: 10 point C1S || # of
Colors: 4C/0 (one-sided) plus overall gloss on front side only || Custom
Specs: Die cut Would like to use an existing die




This assumes that your records are all on a single line (although they may be
displayed on multiple lines -- there cannot be any included line breaks).

This also assumes that each Field type is followed by 2 characters: ": "

With your records in A2:An

Enter the names of the headers in B1:G1

Enter them the same way that they are in the records, e.g.

# of Colors

and not

#ofColors



B2:
=MID($A2,FIND(B$1,$A2)+LEN(B$1)+2,IF(ISERR(FIND(
"||",$A2,FIND(B$1,$A2)+LEN(B$1)+3)),255,FIND("||", $A2,
FIND(B$1,$A2)+LEN(B$1)+3)-(FIND(B$1,$A2)+LEN(B$1)+3)))

Copy/Drag down to Bn.

Then select B2:Bn and copy/drag across to column G

For the fields that are missing, you will see a #VALUE error.

You can hide this with conditional formatting, or you can use an IF statement
to test for each field's presence, and output a null string if it's not there.

B2:
=IF(ISERR(FIND(B$1&": ",$A2)),"",MID($A2,FIND(B$1,$A2)+
LEN(B$1)+2,IF(ISERR(FIND("||",$A2,FIND(B$1,$A2)+
LEN(B$1)+3)),255,FIND("||",$A2,FIND(B$1,$A2)+LEN(
B$1)+3)-(FIND(B$1,$A2)+LEN(B$1)+3))))


--ron

Hi Gloria:
This is what I would try:
Use Text to Column on Sheet1 (records in A1:A100 - or A1000)

On Sheet2 in A1:D1 enter the 'field names': Size, Material, # of colors,
Finish

For each record on Sheet1, in column A you either do or do not have "Size"
So in A2 of Sheet2 use =IF(ISERROR(FIND($A$1,Sheet1!A1)),"",Sheet1!A1)
Copy down the column

For each record on Sheet1, the Material field could be in A (if size is
missing) or in B
So in B2 of Sheet2 use
=IF(ISERROR(FIND($B$1,Sheet1!A1)),"",Sheet1!A1)&IF (ISERROR(FIND($B$1,Sheet1!B1)),"",Sheet1!B1)
Copy down the column

Continue this logic for the other two fields
Hope this works for you or suggests a workable solution
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Gloria Lewis" wrote in message
...
I need to be able to parse my records into seperate columns: Size,
Material,
#ofColors, Coating, Finishing, Custom Specs.

The delimiter is ||. I tried using the Data, Text to Columns options,
however if a record does not contain all of the fields, then it ends up in
the wrong column. How can I parse by looking for the Field Name contained
within the record (i.e. Size:, Material:). Here's my sample data. The
first record has 4 fields (size, material, # of colors, finishing). The
second record has 5 fields (size, material, # of colors, coating,
finishing).
Thanks.

Size: 3.25" x 12.5", folds up to 8.5" || Material: 10 point C2S || # of
Colors: 4/cp 2/sides plus a varnish seal and metallic scratch off one
side.
|| Finishing: Die-cut holes and strip waste. Score at top and perf at
bottom
fold. Seal fold closed with a dot of easy remove waste.

Size: 3-1/4" x 8-3/8" || Material: 10 point C1S || # of Colors: 4C/1C
(two-sided) plus overall gloss on front side only || Coating: Gloss on
front
side only || Finishing: Die-cut hole, strip waste and score.

Size: 3.25" x 12.5" flat, folds up to 3.25" x 8.5" || Material: 10 pt C1S
||
# of Colors: 4/1 || Finishing: Die-cut and strip waste from hole. Score
near
neck and perf where it folds up. Fold and hold in place with a dot of easy
remove glue.

Size: 3.257"w x 8.512"h || Material: 10pt C1S Cover || # of Colors: 4/1 +
Gloss on front side only || Finishing: Die cut hole for neck of bottle and
score

Size: Bottlenecker for the 500 ml Propel bottle. Please confirm the
following will work: 3.75"w x 9.25"h || Material: 10 point C1S || # of
Colors: 4C/0 (one-sided) plus overall gloss on front side only || Custom
Specs: Die cut Would like to use an existing die

Thank you, that answer totally rocked!! It works great.

"Ron Rosenfeld" wrote:

On Mon, 23 Jan 2006 08:32:03 -0800, Gloria Lewis
wrote:

I need to be able to parse my records into seperate columns: Size, Material,
#ofColors, Coating, Finishing, Custom Specs.

The delimiter is ||. I tried using the Data, Text to Columns options,
however if a record does not contain all of the fields, then it ends up in
the wrong column. How can I parse by looking for the Field Name contained
within the record (i.e. Size:, Material:). Here's my sample data. The
first record has 4 fields (size, material, # of colors, finishing). The
second record has 5 fields (size, material, # of colors, coating, finishing).
Thanks.

Size: 3.25" x 12.5", folds up to 8.5" || Material: 10 point C2S || # of
Colors: 4/cp 2/sides plus a varnish seal and metallic scratch off one side.
|| Finishing: Die-cut holes and strip waste. Score at top and perf at bottom
fold. Seal fold closed with a dot of easy remove waste.

Size: 3-1/4" x 8-3/8" || Material: 10 point C1S || # of Colors: 4C/1C
(two-sided) plus overall gloss on front side only || Coating: Gloss on front
side only || Finishing: Die-cut hole, strip waste and score.

Size: 3.25" x 12.5" flat, folds up to 3.25" x 8.5" || Material: 10 pt C1S ||
# of Colors: 4/1 || Finishing: Die-cut and strip waste from hole. Score near
neck and perf where it folds up. Fold and hold in place with a dot of easy
remove glue.

Size: 3.257"w x 8.512"h || Material: 10pt C1S Cover || # of Colors: 4/1 +
Gloss on front side only || Finishing: Die cut hole for neck of bottle and
score

Size: Bottlenecker for the 500 ml Propel bottle. Please confirm the
following will work: 3.75"w x 9.25"h || Material: 10 point C1S || # of
Colors: 4C/0 (one-sided) plus overall gloss on front side only || Custom
Specs: Die cut Would like to use an existing die




This assumes that your records are all on a single line (although they may be
displayed on multiple lines -- there cannot be any included line breaks).

This also assumes that each Field type is followed by 2 characters: ": "

With your records in A2:An

Enter the names of the headers in B1:G1

Enter them the same way that they are in the records, e.g.

# of Colors

and not

#ofColors



B2:
=MID($A2,FIND(B$1,$A2)+LEN(B$1)+2,IF(ISERR(FIND(
"||",$A2,FIND(B$1,$A2)+LEN(B$1)+3)),255,FIND("||", $A2,
FIND(B$1,$A2)+LEN(B$1)+3)-(FIND(B$1,$A2)+LEN(B$1)+3)))

Copy/Drag down to Bn.

Then select B2:Bn and copy/drag across to column G

For the fields that are missing, you will see a #VALUE error.

You can hide this with conditional formatting, or you can use an IF statement
to test for each field's presence, and output a null string if it's not there.

B2:
=IF(ISERR(FIND(B$1&": ",$A2)),"",MID($A2,FIND(B$1,$A2)+
LEN(B$1)+2,IF(ISERR(FIND("||",$A2,FIND(B$1,$A2)+
LEN(B$1)+3)),255,FIND("||",$A2,FIND(B$1,$A2)+LEN(
B$1)+3)-(FIND(B$1,$A2)+LEN(B$1)+3))))


--ron

On Mon, 23 Jan 2006 09:42:02 -0800, Gloria Lewis
wrote:

Thank you, that answer totally rocked!! It works great.


Glad to help. Thanks for the feedback.


--ron