It's late.
We assume source file is in excel format as columns are mentioned.

We do it thus
Sub t()
irow = Cells(Rows.Count, 3).End(xlUp).Row
For i = 1 To irow - 1
If Cells(i + 1, 1) = "" Then Cells(i + 1, 1) = Cells(i, 1)
Next i
For i = 1 To irow
If ((Mid(Range("A" & i), 13, 1) < "-")) Then ipos = 13
If ((Mid(Range("A" & i), 13, 1) = "-")) Then ipos = 14
Cells(i, 1) = Mid(Range("A" & i), ipos, 10)
Next i
For i = irow To 1 Step -1
If Cells(i, 4) = "" Then Rows(i).Delete
Next i
Columns("B:B").Delete
End Sub

--
Regards
PY & Associates
Excel customization service provider



Subject: Tidy up of data within Excel Report
Date: 10/24/2005 1:30 PM PST
From:

Hi,

Incorrectly posted this message on Access Queries by mistake. Please
can you help with the following problem that I've got to sort.

I have data listed, as below, which I need to 'tidy up' to appear as a
flat file.

DATA EXTRACT:
G-ICPR-7203--ZTEA1GL00 Expense policy
kwcx301 smith, John
kcrq254 rogers, nikki
G-ICPR-7203--ZTEA2GL00 expense manager
kgjx668 frame, Shereen
kwsq896 power, max
krdf108 Archer, Michelle
kxfq690 frame, Stephanie
G-ICPR-7203--ZTEA3GL00 Deputy role
krzx217 hough, John
kzkn268 mackay, garry
kdtz927 birch, lilian
G-ICPR-7203-G7203E01 general Role
kwcx301 fishbourne, ronald
kcrq254 abbott, giha
kzcl750 Abbott, Ramil
kwbs802 monk, chip
kgwv200 dibley, duane


The title of the role that people belong to exists in Column A
(G-ICPR-7203--ZTEA1GL00), Description in Column B (e.g. Expense policy)

and then the users who have them roles are on the next row down (ID in
Column C and Name in Column D).

Is there anyway that I can restructure this data automatically (there
is just over 20,000 rows) so this appears:

Column A: Column B: Column C:
ZTEA1GL00 kwcx301 smith, John
ZTEA1GL00 kcrq254 rogers, nikki
ZTEA2GL00 kgjx668 frame, Shereen
ZTEA3GL00 krzx217 hough, John
G7203E01 kwcx301 fishbourne, ronald
etc.. etc... (haven't done the whole example from below, but this shows
what I need).

You can see in the above (column A) that I also only need any part of
the data string after the last - within the data string.

Thanks in advance for your help as always, Al Mackay
)