software

I want to import about 1000 records ( like the 2 below) into excel to
sort. What is the most efficient way to to import these to parse /
format these records to have one row as a record (vba ?? ) ? I know how
to import text files but it doesn't give the formatting options needed
for this file. I can provide details, if you need them.Product Claim

--------------------------------------------------------------------------------

ITEM DETAILS 1 MODEL NUMBER:
E34FCGHJR465G
CODE NUMBER: 45657878345342679 NUMBER:
004753Z
MODEL: 3W45 PROD. ATTRIBUTE
3345678346
MODEL YEAR: 2002 PNC DESCRIPTION
456 4567278
PFP DESCRIPTION: MOUNT

CURRENT SERV. LIFE: 15789 MILES

REPAIR DATE: 20060123

PRODUCTION DATE: 20031002

SOLD DATE: 20050307

CLAIM DATE: 20060301

CHARGE PARTS CHARGE VENDOR RATE 0.6

TOTAL CLAIM AMOUNT: 3060 VENDOR AMOUNT:
10.36
CHARGE SPECIAL HANDLING VENDOR RATE 0.6

TOTAL CLAIM AMOUNT: VENDOR AMOUNT:

CHARGE LABOR CHARGE VENDOR RATE 0.6

TOTAL CLAIM AMOUNT: 1275 VENDOR AMOUNT:
4.356
PRR NUMBER: 1 TROUBLE CODE/DESC.
NOISE
PRR NUMBER: 2 TROUBLE CODE/DESC.
PLAY
HAYON N OUVRE PAS COMPLETEMENT

REMPLACER

REPAIR ACTION#: 1 PART NO:
904507S000
PART QUANTITY: 1 UNIT PRICE:
18.71
REPAIR ACTION#: 2 PART NO:
904517S000
PART QUANTITY: 1 UNIT PRICE:
11.89
REPAIR ACTION#: 3 OPERATION NO:
UM14A1
LABOR HOURS: 0.2

TOTAL CHARGEBACK CLAIM AMOUNT: 26.01

RECEIVER CLAIM NO: 015314060

BUSINESS TYPE PRODUCTION PART

STATE CODE NY

ADJSTMNT MEMO NO: 008677465--------------------------------------------------------------------------------

ITEM DETAILS 2 MODEL NUMBER:
BLJALGA327EU5A----
CODE NUMBER: 1N4BA41E34C852439 NUMBER:
118340Z
MODEL: 6ZV2 PROD. ATTRIBUTE
3566892138
MODEL YEAR: 2004 PNC DESCRIPTION
84532 CASING LID TORS BAR/TAIL
PFP DESCRIPTION: LID ASSY-CASING

CURRENT SERV. LIFE: 6464 MILES

REPAIR DATE: 20060216

PRODUCTION DATE: 20030906

SOLD DATE: 20050331

CLAIM DATE: 20060301

CHARGE PARTS CHARGE VENDOR RATE 0.6

TOTAL CLAIM AMOUNT: 690 VENDOR AMOUNT:
4.14
CHARGE SPECIAL HANDLING VENDOR RATE 0.6

TOTAL CLAIM AMOUNT: VENDOR AMOUNT:

CHARGE LABOR CHARGE VENDOR RATE 0.6

TOTAL CLAIM AMOUNT: 1705 VENDOR AMOUNT:
10.23
PRR NUMBER: 3 TROUBLE CODE/DESC.
POOR GENERAL APPEARANCE
PRR NUMBER: 4 TROUBLE CODE/DESC.
INTERNAL FAILURE
CUSTOMER STATES THE RIGHT REAR CASING SUPPORT HAS COME APART SEE
SPECIAL ORDER PART
THE RIGHT REAR CASING STAY IS BROKEN OFF AT THE
BOTTOM REPLACED THE RIGHT REAR CASING STA
Y UM14AA 2

REPAIR ACTION#: 4 PART NO:
3566892138
PART QUANTITY: 1 UNIT PRICE:
6.9
REPAIR ACTION#: 5 OPERATION NO:
UM14A1
LABOR HOURS: 0.2

TOTAL CHARGEBACK CLAIM AMOUNT: 14.37

RECEIVER CLAIM NO: 015316900

BUSINESS TYPE PRODUCTION PART

STATE CODE MS

ADJSTMNT MEMO NO: 008679905

-----------------------------------------
schnett
------------------------------------------------------------------------
schnett's Profile: www.excelforum.com/member.php...oamp;userid=12035
View this thread: www.excelforum.com/showthread...hreadid=534393
If your data is all a fixed number of rows as per your sample, and
begins in A2, then in B2 put the formula:

=IF(LEFT($A2,12)=quot;ITEM
DETAILSquot;,IF(COLUMN()=2,$A2,INDIRECT(quot;$Aquot;amp;ROW() COL UMN()-2)),quot;quot;)

and formula drag that across to column AG, then (whilst still
selected) formula drag that (B2:AG2) down to the end of your data.

This should provide what you need on the first row of each item, check
column AF

To extract the new form:

-note: To retain your current sequence as the final sequence:
in AH1 put 1, hold the CTRL key and formula drag this to the end of
your data to number each line-.

Select the whole sheet, and Copy, Paste Special, Values back onto
itsself

Delete column A

Select All data and Sort over column B, delete all blank lines.

Select All data and Sort over column AH (which is now column AG)

Delete columns AF and AG

Hope this helps

--

schnett Wrote:
gt; I want to import about 1000 records ( like the 2 below) into excel to
gt; sort. What is the most efficient way to to import these to parse /
gt; format these records to have one row as a record (vba ?? ) ? I know how
gt; to import text files but it doesn't give the formatting options needed
gt; for this file. I can provide details, if you need them.
gt;
gt;
gt; Product Claim
gt;
gt; --------------------------------------------------------------------------------
gt;
gt; ITEM DETAILS 1 MODEL NUMBER:
gt; E34FCGHJR465G
gt; CODE NUMBER: 45657878345342679 NUMBER:
gt; 004753Z
gt; MODEL: 3W45 PROD. ATTRIBUTE
gt; 3345678346
gt; MODEL YEAR: 2002 PNC DESCRIPTION
gt; 456 4567278
gt; PFP DESCRIPTION: MOUNT
gt;
gt; CURRENT SERV. LIFE: 15789 MILES
gt;
gt; REPAIR DATE: 20060123
gt;
gt; PRODUCTION DATE: 20031002
gt;
gt; SOLD DATE: 20050307
gt;
gt; CLAIM DATE: 20060301
gt;
gt; CHARGE PARTS CHARGE VENDOR RATE 0.6
gt;
gt; TOTAL CLAIM AMOUNT: 3060 VENDOR AMOUNT:
gt; 10.36
gt; CHARGE SPECIAL HANDLING VENDOR RATE 0.6
gt;
gt; TOTAL CLAIM AMOUNT: VENDOR AMOUNT:
gt;
gt; CHARGE LABOR CHARGE VENDOR RATE 0.6
gt;
gt; TOTAL CLAIM AMOUNT: 1275 VENDOR AMOUNT:
gt; 4.356
gt; PRR NUMBER: 1 TROUBLE CODE/DESC.
gt; NOISE
gt; PRR NUMBER: 2 TROUBLE CODE/DESC.
gt; PLAY
gt; HAYON N OUVRE PAS COMPLETEMENT
gt;
gt; REMPLACER
gt;
gt; REPAIR ACTION#: 1 PART NO:
gt; 904507S000
gt; PART QUANTITY: 1 UNIT PRICE:
gt; 18.71
gt; REPAIR ACTION#: 2 PART NO:
gt; 904517S000
gt; PART QUANTITY: 1 UNIT PRICE:
gt; 11.89
gt; REPAIR ACTION#: 3 OPERATION NO:
gt; UM14A1
gt; LABOR HOURS: 0.2
gt;
gt; TOTAL CHARGEBACK CLAIM AMOUNT: 26.01
gt;
gt; RECEIVER CLAIM NO: 015314060
gt;
gt; BUSINESS TYPE PRODUCTION PART
gt;
gt; STATE CODE NY
gt;
gt; ADJSTMNT MEMO NO: 008677465
gt;
gt;
gt; --------------------------------------------------------------------------------
gt;
gt; ITEM DETAILS 2 MODEL NUMBER:
gt; BLJALGA327EU5A----
gt; CODE NUMBER: 1N4BA41E34C852439 NUMBER:
gt; 118340Z
gt; MODEL: 6ZV2 PROD. ATTRIBUTE
gt; 3566892138
gt; MODEL YEAR: 2004 PNC DESCRIPTION
gt; 84532 CASING LID TORS BAR/TAIL
gt; PFP DESCRIPTION: LID ASSY-CASING
gt;
gt; CURRENT SERV. LIFE: 6464 MILES
gt;
gt; REPAIR DATE: 20060216
gt;
gt; PRODUCTION DATE: 20030906
gt;
gt; SOLD DATE: 20050331
gt;
gt; CLAIM DATE: 20060301
gt;
gt; CHARGE PARTS CHARGE VENDOR RATE 0.6
gt;
gt; TOTAL CLAIM AMOUNT: 690 VENDOR AMOUNT:
gt; 4.14
gt; CHARGE SPECIAL HANDLING VENDOR RATE 0.6
gt;
gt; TOTAL CLAIM AMOUNT: VENDOR AMOUNT:
gt;
gt; CHARGE LABOR CHARGE VENDOR RATE 0.6
gt;
gt; TOTAL CLAIM AMOUNT: 1705 VENDOR AMOUNT:
gt; 10.23
gt; PRR NUMBER: 3 TROUBLE CODE/DESC.
gt; POOR GENERAL APPEARANCE
gt; PRR NUMBER: 4 TROUBLE CODE/DESC.
gt; INTERNAL FAILURE
gt; CUSTOMER STATES THE RIGHT REAR CASING SUPPORT HAS COME APART SEE
gt; SPECIAL ORDER PART
gt; THE RIGHT REAR CASING STAY IS BROKEN OFF AT THE
gt; BOTTOM REPLACED THE RIGHT REAR CASING STA
gt; Y UM14AA 2
gt;
gt; REPAIR ACTION#: 4 PART NO:
gt; 3566892138
gt; PART QUANTITY: 1 UNIT PRICE:
gt; 6.9
gt; REPAIR ACTION#: 5 OPERATION NO:
gt; UM14A1
gt; LABOR HOURS: 0.2
gt;
gt; TOTAL CHARGEBACK CLAIM AMOUNT: 14.37
gt;
gt; RECEIVER CLAIM NO: 015316900
gt;
gt; BUSINESS TYPE PRODUCTION PART
gt;
gt; STATE CODE MS
gt;
gt; ADJSTMNT MEMO NO: 008679905
gt;
gt; -----------------------------------------
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=534393The information provided appears to be one row of data, separated by a blank
row;
usually four fields per row except Trouble Code/Desc and the last 5 or 6
rows;
1st row identify as Item Details
last row identify as Adjustment Memo No

You are wanting to put field names across row 1
and put data from row 2 onwards so that you can sort.

We consider you need to use VBA to achieve this.
If this is one off job, we believe it is more cost effective to pay some
service provider to do.

quot;schnettquot; gt; wrote in
message ...
gt;
gt; I want to import about 1000 records ( like the 2 below) into excel to
gt; sort. What is the most efficient way to to import these to parse /
gt; format these records to have one row as a record (vba ?? ) ? I know how
gt; to import text files but it doesn't give the formatting options needed
gt; for this file. I can provide details, if you need them.
gt;
gt;
gt; Product Claim
gt;
gt; --------------------------------------------------------------------------
------
gt;
gt; ITEM DETAILS 1 MODEL NUMBER:
gt; E34FCGHJR465G
gt; CODE NUMBER: 45657878345342679 NUMBER:
gt; 004753Z
gt; MODEL: 3W45 PROD. ATTRIBUTE
gt; 3345678346
gt; MODEL YEAR: 2002 PNC DESCRIPTION
gt; 456 4567278
gt; PFP DESCRIPTION: MOUNT
gt;
gt; CURRENT SERV. LIFE: 15789 MILES
gt;
gt; REPAIR DATE: 20060123
gt;
gt; PRODUCTION DATE: 20031002
gt;
gt; SOLD DATE: 20050307
gt;
gt; CLAIM DATE: 20060301
gt;
gt; CHARGE PARTS CHARGE VENDOR RATE 0.6
gt;
gt; TOTAL CLAIM AMOUNT: 3060 VENDOR AMOUNT:
gt; 10.36
gt; CHARGE SPECIAL HANDLING VENDOR RATE 0.6
gt;
gt; TOTAL CLAIM AMOUNT: VENDOR AMOUNT:
gt;
gt; CHARGE LABOR CHARGE VENDOR RATE 0.6
gt;
gt; TOTAL CLAIM AMOUNT: 1275 VENDOR AMOUNT:
gt; 4.356
gt; PRR NUMBER: 1 TROUBLE CODE/DESC.
gt; NOISE
gt; PRR NUMBER: 2 TROUBLE CODE/DESC.
gt; PLAY
gt; HAYON N OUVRE PAS COMPLETEMENT
gt;
gt; REMPLACER
gt;
gt; REPAIR ACTION#: 1 PART NO:
gt; 904507S000
gt; PART QUANTITY: 1 UNIT PRICE:
gt; 18.71
gt; REPAIR ACTION#: 2 PART NO:
gt; 904517S000
gt; PART QUANTITY: 1 UNIT PRICE:
gt; 11.89
gt; REPAIR ACTION#: 3 OPERATION NO:
gt; UM14A1
gt; LABOR HOURS: 0.2
gt;
gt; TOTAL CHARGEBACK CLAIM AMOUNT: 26.01
gt;
gt; RECEIVER CLAIM NO: 015314060
gt;
gt; BUSINESS TYPE PRODUCTION PART
gt;
gt; STATE CODE NY
gt;
gt; ADJSTMNT MEMO NO: 008677465
gt;
gt;
gt; --------------------------------------------------------------------------
------
gt;
gt; ITEM DETAILS 2 MODEL NUMBER:
gt; BLJALGA327EU5A----
gt; CODE NUMBER: 1N4BA41E34C852439 NUMBER:
gt; 118340Z
gt; MODEL: 6ZV2 PROD. ATTRIBUTE
gt; 3566892138
gt; MODEL YEAR: 2004 PNC DESCRIPTION
gt; 84532 CASING LID TORS BAR/TAIL
gt; PFP DESCRIPTION: LID ASSY-CASING
gt;
gt; CURRENT SERV. LIFE: 6464 MILES
gt;
gt; REPAIR DATE: 20060216
gt;
gt; PRODUCTION DATE: 20030906
gt;
gt; SOLD DATE: 20050331
gt;
gt; CLAIM DATE: 20060301
gt;
gt; CHARGE PARTS CHARGE VENDOR RATE 0.6
gt;
gt; TOTAL CLAIM AMOUNT: 690 VENDOR AMOUNT:
gt; 4.14
gt; CHARGE SPECIAL HANDLING VENDOR RATE 0.6
gt;
gt; TOTAL CLAIM AMOUNT: VENDOR AMOUNT:
gt;
gt; CHARGE LABOR CHARGE VENDOR RATE 0.6
gt;
gt; TOTAL CLAIM AMOUNT: 1705 VENDOR AMOUNT:
gt; 10.23
gt; PRR NUMBER: 3 TROUBLE CODE/DESC.
gt; POOR GENERAL APPEARANCE
gt; PRR NUMBER: 4 TROUBLE CODE/DESC.
gt; INTERNAL FAILURE
gt; CUSTOMER STATES THE RIGHT REAR CASING SUPPORT HAS COME APART SEE
gt; SPECIAL ORDER PART
gt; THE RIGHT REAR CASING STAY IS BROKEN OFF AT THE
gt; BOTTOM REPLACED THE RIGHT REAR CASING STA
gt; Y UM14AA 2
gt;
gt; REPAIR ACTION#: 4 PART NO:
gt; 3566892138
gt; PART QUANTITY: 1 UNIT PRICE:
gt; 6.9
gt; REPAIR ACTION#: 5 OPERATION NO:
gt; UM14A1
gt; LABOR HOURS: 0.2
gt;
gt; TOTAL CHARGEBACK CLAIM AMOUNT: 14.37
gt;
gt; RECEIVER CLAIM NO: 015316900
gt;
gt; BUSINESS TYPE PRODUCTION PART
gt;
gt; STATE CODE MS
gt;
gt; ADJSTMNT MEMO NO: 008679905
gt;
gt; ---------------------------------------
gt;
gt;
gt; --
gt; schnett
gt; ------------------------------------------------------------------------
gt; schnett's Profile:
www.excelforum.com/member.php...oamp;userid=12035
gt; View this thread: www.excelforum.com/showthread...hreadid=534393
gt;

Well spotted, I read the question but not the data.

Row 1 appears to be 2 fields, sequence and Model Number but only one
':' seperator, row 2 has two ':' sperators, row 3 and 4 only one,
whilst others, like the Vendor rates, have none.

Could be an interesting VB parse unless the OP could re-extract
dropping the headers, I cannot see that the data will support any form
of text-to-columns separation.

--PY amp; Associates Wrote:
gt; The information provided appears to be one row of data, separated by a
gt; blank
gt; row;
gt; usually four fields per row except Trouble Code/Desc and the last 5 or
gt; 6
gt; rows;
gt; 1st row identify as Item Details
gt; last row identify as Adjustment Memo No
gt;
gt; You are wanting to put field names across row 1
gt; and put data from row 2 onwards so that you can sort.
gt;
gt; We consider you need to use VBA to achieve this.
gt; If this is one off job, we believe it is more cost effective to pay
gt; some
gt; service provider to do.
gt;
gt; quot;schnettquot; gt; wrote
gt; in
gt; message ...
gt; gt;
gt; gt; I want to import about 1000 records ( like the 2 below) into excel
gt; to
gt; gt; sort. What is the most efficient way to to import these to parse /
gt; gt; format these records to have one row as a record (vba ?? ) ? I know
gt; how
gt; gt; to import text files but it doesn't give the formatting options
gt; needed
gt; gt; for this file. I can provide details, if you need them.
gt; gt;
gt; gt;
gt; gt; Product Claim
gt; gt;
gt; gt;
gt; --------------------------------------------------------------------------
gt; ------
gt; gt;
gt; gt; ITEM DETAILS 1 MODEL NUMBER:
gt; gt; E34FCGHJR465G
gt; gt; CODE NUMBER: 45657878345342679 NUMBER:
gt; gt; 004753Z
gt; gt; MODEL: 3W45 PROD. ATTRIBUTE
gt; gt; 3345678346
gt; gt; MODEL YEAR: 2002 PNC DESCRIPTION
gt; gt; 456 4567278
gt; gt; PFP DESCRIPTION: MOUNT
gt; gt;
gt; gt; CURRENT SERV. LIFE: 15789 MILES
gt; gt;
gt; gt; REPAIR DATE: 20060123
gt; gt;
gt; gt; PRODUCTION DATE: 20031002
gt; gt;
gt; gt; SOLD DATE: 20050307
gt; gt;
gt; gt; CLAIM DATE: 20060301
gt; gt;
gt; gt; CHARGE PARTS CHARGE VENDOR RATE 0.6
gt; gt;
gt; gt; TOTAL CLAIM AMOUNT: 3060 VENDOR AMOUNT:
gt; gt; 10.36
gt; gt; CHARGE SPECIAL HANDLING VENDOR RATE 0.6
gt; gt;
gt; gt; TOTAL CLAIM AMOUNT: VENDOR AMOUNT:
gt; gt;
gt; gt; CHARGE LABOR CHARGE VENDOR RATE 0.6
gt; gt;
gt; gt; TOTAL CLAIM AMOUNT: 1275 VENDOR AMOUNT:
gt; gt; 4.356
gt; gt; PRR NUMBER: 1 TROUBLE CODE/DESC.
gt; gt; NOISE
gt; gt; PRR NUMBER: 2 TROUBLE CODE/DESC.
gt; gt; PLAY
gt; gt; HAYON N OUVRE PAS COMPLETEMENT
gt; gt;
gt; gt; REMPLACER
gt; gt;
gt; gt; REPAIR ACTION#: 1 PART NO:
gt; gt; 904507S000
gt; gt; PART QUANTITY: 1 UNIT PRICE:
gt; gt; 18.71
gt; gt; REPAIR ACTION#: 2 PART NO:
gt; gt; 904517S000
gt; gt; PART QUANTITY: 1 UNIT PRICE:
gt; gt; 11.89
gt; gt; REPAIR ACTION#: 3 OPERATION NO:
gt; gt; UM14A1
gt; gt; LABOR HOURS: 0.2
gt; gt;
gt; gt; TOTAL CHARGEBACK CLAIM AMOUNT: 26.01
gt; gt;
gt; gt; RECEIVER CLAIM NO: 015314060
gt; gt;
gt; gt; BUSINESS TYPE PRODUCTION PART
gt; gt;
gt; gt; STATE CODE NY
gt; gt;
gt; gt; ADJSTMNT MEMO NO: 008677465
gt; gt;
gt; gt;
gt; gt;
gt; --------------------------------------------------------------------------
gt; ------
gt; gt;
gt; gt; ITEM DETAILS 2 MODEL NUMBER:
gt; gt; BLJALGA327EU5A----
gt; gt; CODE NUMBER: 1N4BA41E34C852439 NUMBER:
gt; gt; 118340Z
gt; gt; MODEL: 6ZV2 PROD. ATTRIBUTE
gt; gt; 3566892138
gt; gt; MODEL YEAR: 2004 PNC DESCRIPTION
gt; gt; 84532 CASING LID TORS BAR/TAIL
gt; gt; PFP DESCRIPTION: LID ASSY-CASING
gt; gt;
gt; gt; CURRENT SERV. LIFE: 6464 MILES
gt; gt;
gt; gt; REPAIR DATE: 20060216
gt; gt;
gt; gt; PRODUCTION DATE: 20030906
gt; gt;
gt; gt; SOLD DATE: 20050331
gt; gt;
gt; gt; CLAIM DATE: 20060301
gt; gt;
gt; gt; CHARGE PARTS CHARGE VENDOR RATE 0.6
gt; gt;
gt; gt; TOTAL CLAIM AMOUNT: 690 VENDOR AMOUNT:
gt; gt; 4.14
gt; gt; CHARGE SPECIAL HANDLING VENDOR RATE 0.6
gt; gt;
gt; gt; TOTAL CLAIM AMOUNT: VENDOR AMOUNT:
gt; gt;
gt; gt; CHARGE LABOR CHARGE VENDOR RATE 0.6
gt; gt;
gt; gt; TOTAL CLAIM AMOUNT: 1705 VENDOR AMOUNT:
gt; gt; 10.23
gt; gt; PRR NUMBER: 3 TROUBLE CODE/DESC.
gt; gt; POOR GENERAL APPEARANCE
gt; gt; PRR NUMBER: 4 TROUBLE CODE/DESC.
gt; gt; INTERNAL FAILURE
gt; gt; CUSTOMER STATES THE RIGHT REAR CASING SUPPORT HAS COME APART SEE
gt; gt; SPECIAL ORDER PART
gt; gt; THE RIGHT REAR CASING STAY IS BROKEN OFF AT THE
gt; gt; BOTTOM REPLACED THE RIGHT REAR CASING STA
gt; gt; Y UM14AA 2
gt; gt;
gt; gt; REPAIR ACTION#: 4 PART NO:
gt; gt; 3566892138
gt; gt; PART QUANTITY: 1 UNIT PRICE:
gt; gt; 6.9
gt; gt; REPAIR ACTION#: 5 OPERATION NO:
gt; gt; UM14A1
gt; gt; LABOR HOURS: 0.2
gt; gt;
gt; gt; TOTAL CHARGEBACK CLAIM AMOUNT: 14.37
gt; gt;
gt; gt; RECEIVER CLAIM NO: 015316900
gt; gt;
gt; gt; BUSINESS TYPE PRODUCTION PART
gt; gt;
gt; gt; STATE CODE MS
gt; gt;
gt; gt; ADJSTMNT MEMO NO: 008679905
gt; gt;
gt; gt; ---------------------------------------
gt; gt;
gt; gt;
gt; gt; --
gt; gt; schnett
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; schnett's Profile:
gt; www.excelforum.com/member.php...oamp;userid=12035
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=534393
gt; gt;--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=534393
Just of interest for schnett, the attached formula (across Row 2) will
show roughly what will be required in specs for parsing your data, and
I guess highlight the need to, if possible, have the file supplied in a
set format.

Hope this assists you

File:www.excelforum.com/attachment...3amp;d=1145536793

--

Bryan Hessey Wrote:
gt; Well spotted, I read the question but not the data.
gt;
gt; Row 1 appears to be 2 fields, sequence and Model Number but only one
gt; ':' seperator, row 2 has two ':' sperators, row 3 and 4 only one,
gt; whilst others, like the Vendor rates, have none.
gt;
gt; Could be an interesting VB parse unless the OP could re-extract
gt; dropping the headers, I cannot see that the data will support any form
gt; of text-to-columns separation.
gt;
gt; -- -------------------------------------------------------------------
|Filename: Book1v.zip |
|Download: www.excelforum.com/attachment.php?postid=4663 |
-------------------------------------------------------------------

--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=534393You should note that the field before Repair Action #1 is irregular. Who
knows what surprises lie in other set of data?

quot;Bryan Hesseyquot; gt;
wrote in message
news:Bryan.Hessey.26kcgy_1145537100.9247@excelforu m-nospam.com...
gt;
gt; Just of interest for schnett, the attached formula (across Row 2) will
gt; show roughly what will be required in specs for parsing your data, and
gt; I guess highlight the need to, if possible, have the file supplied in a
gt; set format.
gt;
gt; Hope this assists you
gt;
gt;
File:www.excelforum.com/attachment...3amp;d=1145536793
gt;
gt; --
gt;
gt; Bryan Hessey Wrote:
gt; gt; Well spotted, I read the question but not the data.
gt; gt;
gt; gt; Row 1 appears to be 2 fields, sequence and Model Number but only one
gt; gt; ':' seperator, row 2 has two ':' sperators, row 3 and 4 only one,
gt; gt; whilst others, like the Vendor rates, have none.
gt; gt;
gt; gt; Could be an interesting VB parse unless the OP could re-extract
gt; gt; dropping the headers, I cannot see that the data will support any form
gt; gt; of text-to-columns separation.
gt; gt;
gt; gt; --
gt;
gt;
gt; -------------------------------------------------------------------
gt; |Filename: Book1v.zip |
gt; |Download: www.excelforum.com/attachment.php?postid=4663 |
gt; -------------------------------------------------------------------
gt;
gt; --
gt; Bryan Hessey
gt; ------------------------------------------------------------------------
gt; Bryan Hessey's Profile:
www.excelforum.com/member.php...oamp;userid=21059
gt; View this thread: www.excelforum.com/showthread...hreadid=534393
gt;

Also that one line of data was split over two lines, and the second Set
was 2 lines shorter than the first, hence my comment that, if possible,
a more standard format would make life easier.

--

PY amp; Associates Wrote:
gt; You should note that the field before Repair Action #1 is irregular.
gt; Who
gt; knows what surprises lie in other set of data?
gt;
gt; quot;Bryan Hesseyquot;
gt; gt;
gt; wrote in message
gt; news:Bryan.Hessey.26kcgy_1145537100.9247@excelforu m-nospam.com...
gt; gt;
gt; gt; Just of interest for schnett, the attached formula (across Row 2)
gt; will
gt; gt; show roughly what will be required in specs for parsing your data,
gt; and
gt; gt; I guess highlight the need to, if possible, have the file supplied in
gt; a
gt; gt; set format.
gt; gt;
gt; gt; Hope this assists you
gt; gt;
gt; gt;
gt; File:www.excelforum.com/attachment...3amp;d=1145536793
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Bryan Hessey Wrote:
gt; gt; gt; Well spotted, I read the question but not the data.
gt; gt; gt;
gt; gt; gt; Row 1 appears to be 2 fields, sequence and Model Number but only
gt; one
gt; gt; gt; ':' seperator, row 2 has two ':' sperators, row 3 and 4 only one,
gt; gt; gt; whilst others, like the Vendor rates, have none.
gt; gt; gt;
gt; gt; gt; Could be an interesting VB parse unless the OP could re-extract
gt; gt; gt; dropping the headers, I cannot see that the data will support any
gt; form
gt; gt; gt; of text-to-columns separation.
gt; gt; gt;
gt; gt; gt; --
gt; gt;
gt; gt;
gt; gt;
gt; -------------------------------------------------------------------
gt; gt; |Filename: Book1v.zip
gt; |
gt; gt; |Download: www.excelforum.com/attachment.php?postid=4663
gt; |
gt; gt;
gt; -------------------------------------------------------------------
gt; gt;
gt; gt; --
gt; gt; Bryan Hessey
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; Bryan Hessey's Profile:
gt; www.excelforum.com/member.php...oamp;userid=21059
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=534393
gt; gt;--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=534393
Also that one line of data was split over two lines, and the second Set
was 2 lines shorter than the first, hence my comment that, if possible,
a more standard format would make life easier.

However, with only 1,000 lines it might be quicker to a 'as-is' job and
manually fix it afterwards.

--

PY amp; Associates Wrote:
gt; You should note that the field before Repair Action #1 is irregular.
gt; Who
gt; knows what surprises lie in other set of data?
gt;
gt; quot;Bryan Hesseyquot;
gt; gt;
gt; wrote in message
gt; news:Bryan.Hessey.26kcgy_1145537100.9247@excelforu m-nospam.com...
gt; gt;
gt; gt; Just of interest for schnett, the attached formula (across Row 2)
gt; will
gt; gt; show roughly what will be required in specs for parsing your data,
gt; and
gt; gt; I guess highlight the need to, if possible, have the file supplied in
gt; a
gt; gt; set format.
gt; gt;
gt; gt; Hope this assists you
gt; gt;
gt; gt;
gt; File:www.excelforum.com/attachment...3amp;d=1145536793
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Bryan Hessey Wrote:
gt; gt; gt; Well spotted, I read the question but not the data.
gt; gt; gt;
gt; gt; gt; Row 1 appears to be 2 fields, sequence and Model Number but only
gt; one
gt; gt; gt; ':' seperator, row 2 has two ':' sperators, row 3 and 4 only one,
gt; gt; gt; whilst others, like the Vendor rates, have none.
gt; gt; gt;
gt; gt; gt; Could be an interesting VB parse unless the OP could re-extract
gt; gt; gt; dropping the headers, I cannot see that the data will support any
gt; form
gt; gt; gt; of text-to-columns separation.
gt; gt; gt;
gt; gt; gt; --
gt; gt;
gt; gt;
gt; gt;
gt; -------------------------------------------------------------------
gt; gt; |Filename: Book1v.zip
gt; |
gt; gt; |Download: www.excelforum.com/attachment.php?postid=4663
gt; |
gt; gt;
gt; -------------------------------------------------------------------
gt; gt;
gt; gt; --
gt; gt; Bryan Hessey
gt; gt;
gt; ------------------------------------------------------------------------
gt; gt; Bryan Hessey's Profile:
gt; www.excelforum.com/member.php...oamp;userid=21059
gt; gt; View this thread:
gt; www.excelforum.com/showthread...hreadid=534393
gt; gt;--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=534393
I want to thank you for all that formula coding that went into that
attachment sheet.

This raw attachment is only a sample of the records. The records seem
more regular than irregular.

I tried what you had suggested and it would have worked except there is
a blank space in front of all my characters. Here is the raw data file.
Can you teach me what the formulas in order to extract data from these
records just like you did before ?

Thank you ! -------------------------------------------------------------------
|Filename: Spc data.txt |
|Download: www.excelforum.com/attachment.php?postid=4668 |
-------------------------------------------------------------------

--
schnett
------------------------------------------------------------------------
schnett's Profile: www.excelforum.com/member.php...oamp;userid=12035
View this thread: www.excelforum.com/showthread...hreadid=534393
Hi,

A quick play with your file, opened in MS Word, replaced quot; quot; (3
spaces) by tab, saveas DOS text. In Excel, Data, import external, in
Wizard take Tab and colon : as delimiters, treat consecutive delimiters
as one, into A1

That gave A to L in the attached.

There were two spaces, not 1, in front of your data.

Set the formula in H2 as

=IF(LEFT(B2,2)=quot; quot;,MID(B2,3,999),IF(LEFT(B2,1)=quot;
quot;,MID(B2,2,999),IF(B2lt;gt;quot;quot;,B2,quot;quot;)))

copied across, and bulk copied down.

That gave columns H to L

With that range still selected, did Copy and Paste Special Values into
cell N2

That gave columns N O P and Q as your partially cleaned data.

Columns A to M can be deleted, they were just to show.

Does this help?

will try the new formula n that

--

schnett Wrote:
gt; I want to thank you for all that formula coding that went into that
gt; attachment sheet.
gt;
gt; This raw attachment is only a sample of the records. The records seem
gt; more regular than irregular.
gt;
gt; I tried what you had suggested and it would have worked except there is
gt; a blank space in front of all my characters. Here is the raw data file.
gt; Can you teach me what the formulas in order to extract data from these
gt; records just like you did before ?
gt;
gt; Thank you ! -------------------------------------------------------------------
|Filename: Spc.zip |
|Download: www.excelforum.com/attachment.php?postid=4669 |
-------------------------------------------------------------------

--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: www.excelforum.com/member.php...oamp;userid=21059
View this thread: www.excelforum.com/showthread...hreadid=534393