Help ! Ive copied a price list of a PDF file and need to put it back
into original columns in an Excel file.
I tried Text to columns but as the descriptions vary in length it didnt
work & to do manually would take a while cause there is about 800 to
do.
The columns need to be set out like this -
Number. Code. Description. EACH. Price.
First few below


1 BB-1a Open/check/seal 300/900/1800 type CCU (includes up to 60 tags)
EACH $32.90
2 BB-1b Open/check/seal 240/480/CMUX Line RIM Housing (seals and
designations only) EACH $21.55
3 BB-1c Replace Captive nut (steel); Lock (pillar); Cover Top
plate;Sealing plug or Centre stud EACH $17.04
4 BB-1e Replace CCU Cap EACH $106.41


--
ferg
------------------------------------------------------------------------
ferg's Profile: http://www.excelforum.com/member.php...o&userid=36519
View this thread: http://www.excelforum.com/showthread...hreadid=569408

typical parsing is either by columns - no good in your case
or by space/tab/comma etc - not obvious on your data

First I'd edit the original data, ie change EACH to !EACH
Then load text parsing on !
Then after inserting enough empty columns (you can always delete them
later)
re-parse column 1, with fixed size to seperate the BB- fromt he description
In theory you should now have 3 columns
BB-.. in first
Open/check...etc in 2nd
and EACH $... in the last

For that last column Edit/replace "EACH " with null

Parsing is only a one off operation, if the data was correctly set up for
parsing :)

Steve

On Tue, 08 Aug 2006 14:44:10 +0100, ferg
wrote:


Help ! Ive copied a price list of a PDF file and need to put it back
into original columns in an Excel file.
I tried Text to columns but as the descriptions vary in length it didnt
work & to do manually would take a while cause there is about 800 to
do.
The columns need to be set out like this -
Number. Code. Description. EACH. Price.
First few below


1 BB-1a Open/check/seal 300/900/1800 type CCU (includes up to 60 tags)
EACH $32.90
2 BB-1b Open/check/seal 240/480/CMUX Line RIM Housing (seals and
designations only) EACH $21.55
3 BB-1c Replace Captive nut (steel); Lock (pillar); Cover Top
plate;Sealing plug or Centre stud EACH $17.04
4 BB-1e Replace CCU Cap EACH $106.41





--
Steve (3)

This formula, copied down, will extract your 2 character "Number"
=LEFT(A1,2)
This formula, copied down, will extract your 2 character "Code"
=MID(A1,FIND("-",A1,1)+1,2)
This formula, copied down, will extract your "Description"
=MID(A1,FIND("-",A1,1)+4,FIND("EACH",A1,1))
Then simply type EACH and copy down for that column
This formula, copied down, will extract your "Price"
=MID(A1,FIND("EACH",A1,1)+5,99)

hth
Vaya con Dios,
Chuck, CABGx3



"ferg" wrote:


Help ! Ive copied a price list of a PDF file and need to put it back
into original columns in an Excel file.
I tried Text to columns but as the descriptions vary in length it didnt
work & to do manually would take a while cause there is about 800 to
do.
The columns need to be set out like this -
Number. Code. Description. EACH. Price.
First few below


1 BB-1a Open/check/seal 300/900/1800 type CCU (includes up to 60 tags)
EACH $32.90
2 BB-1b Open/check/seal 240/480/CMUX Line RIM Housing (seals and
designations only) EACH $21.55
3 BB-1c Replace Captive nut (steel); Lock (pillar); Cover Top
plate;Sealing plug or Centre stud EACH $17.04
4 BB-1e Replace CCU Cap EACH $106.41


--
ferg
------------------------------------------------------------------------
ferg's Profile: http://www.excelforum.com/member.php...o&userid=36519
View this thread: http://www.excelforum.com/showthread...hreadid=569408

Did I miss something, why wouldn't he be able to copy
columns from PDF to Excel one by one. It's better than
nothing. Or nothing is worse than something.

PDF and Excel
http://www.mvps.org/dmcritchie/excel/pdf.htm

If the rows don't match up when you are finished perhaps
http://www.mvps.org/dmcritchie/excel/delempty.htm
might have some suggestions depending on how bad it looks.

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"SteveW" wrote in message news:op.tdyykbs5evjsnp@enigma03...
typical parsing is either by columns - no good in your case
or by space/tab/comma etc - not obvious on your data

First I'd edit the original data, ie change EACH to !EACH
Then load text parsing on !
Then after inserting enough empty columns (you can always delete them
later)
re-parse column 1, with fixed size to seperate the BB- fromt he description
In theory you should now have 3 columns
BB-.. in first
Open/check...etc in 2nd
and EACH $... in the last

For that last column Edit/replace "EACH " with null

Parsing is only a one off operation, if the data was correctly set up for
parsing :)

Steve

On Tue, 08 Aug 2006 14:44:10 +0100, ferg
wrote:


Help ! Ive copied a price list of a PDF file and need to put it back
into original columns in an Excel file.
I tried Text to columns but as the descriptions vary in length it didnt
work & to do manually would take a while cause there is about 800 to
do.
The columns need to be set out like this -
Number. Code. Description. EACH. Price.
First few below


1 BB-1a Open/check/seal 300/900/1800 type CCU (includes up to 60 tags)
EACH $32.90
2 BB-1b Open/check/seal 240/480/CMUX Line RIM Housing (seals and
designations only) EACH $21.55
3 BB-1c Replace Captive nut (steel); Lock (pillar); Cover Top
plate;Sealing plug or Centre stud EACH $17.04
4 BB-1e Replace CCU Cap EACH $106.41





--
Steve (3)