auto format

  • Thread starter Thread starter toby
  • Start date Start date
T

toby

hi .
if i hv different paragraphs of data in word like this ,
30. ISBN/ISSN = abc.

AUTHOR = bcd.

TITLE = cde.

IMPRINT = def.

SERIES = efg.



31. ISBN/ISSN = cba.

AUTHOR = dcb.

TITLE = edc.

IMPRINT = fed.

SERIES = gfe.



would it be possible to import the data into the excel and rearrange the
data format in forms of columns!? and how to make it.?!

such as

column >> isbn/issn, author, title, imprint, series

abc bcd cde def
efg

cba dcb edc fed
gfe

thanks in advance.
 
One way to try ..

Copy from Word

In Excel:

In Sheet1
-------------
Right-click on A1 > Paste special > Text > OK
Press F5 > Special > Blanks > OK
(this selects all the in-between blank rows)
Right-click on selection > Delete > Shift cells up > OK
(this'll delete all the blank rows)

Select col A
Click Data > Text to Columns > Delimited > Next
Check "Space" > Finish
(Above will split col A into 4 cols: A to D)

In Sheet2
-------------
Copy a typical set of 5 col headers, say from B1:B5 of Sheet1
Right-click on B1 (in Sheet2) > Paste special > transpose > OK
(This puts the 5 col headers ISBN ... SERIES into B1:F1)

Put in A2: =OFFSET(Sheet1!$A$1,ROW(A1)*5-5,)

Put in B2: =OFFSET(Sheet1!$D$1,ROW(A1)*5-5+COLUMN(A1)-1,)
Copy B2 across to F2

Select A2:F2, fill down until zeros appear (signalling end of data)
 
Back
Top