Parsing Problem

[Gloria Lewis]

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

 

[Dave O]

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

 

[Gloria Lewis]

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

 

[Ron Rosenfeld]

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

 

[Bernard Liengme]

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

On Sheet2 in A11 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

 

[Gloria Lewis]

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

 

[Ron Rosenfeld]

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

Glad to help. Thanks for the feedback.


--ron