Simple Copy

[rcsberg]

How can I extract the year "1851" from "AL011851" and copy that year for 14 rows. This process repeats with the variables being the year and the number of rows for that particular year. The number of rows to copy is given in a certain cell in column F.

There are 52,000 rows in total.​

Example 1:
E

xtract the year "1851" from "AL011851" (in column 1) and copy the 4-digit year to another column for 14 rows.

Example 2:
Extract the year "1851" from "AL021851"

(in column 1) and copy the 4-digit year to another column​

for 1 row.

Example 3:
Extract the year "1980" from "AL021980"

(in column 1) and copy the 4-digit year to another column​

for 49 rows.​

Etc...for 52,000 rows.

Thank you in advance!

 

[DanteAmor]

Hi @★ rcsberg, welcome to the forum!

Any doubts:
- The year is always the 4 digits to the right of the text?
- How to know how many rows the year should be copied?
- In which other column and in which rows should the year be pasted?
- You could give an example of how your data looks. Just copy the range of your cells and paste here. Or upload an image to dropbox.

 

[rcsberg]

Hi Dante,

Thanks for the reply.

- The year is always the 4 digits to the right of the text?
1. Column A has the storm name number "AL011851" with year to the right, followed by successive updates for the storm. Each update "18510625" is written with the year to the right.
- How to know how many rows the year should be copied?
1. Column E has the number of rows for each storm.

Column A has the storm name number "AL011851" with year to the right, followed by successive updates for the storm. Each update "18510625" is written with the year to the right and month and day to the left.
Column B only has the storm name number "AL011851"
Column C has the actual name of the storm "UNNAMED"
Column D is a duplicate of Column C
Column E has the number of rows representing the updates for each storm "14"


I hope this table is formatted correctly for you to read. Hope this helps.


A B C D E
AL011851 AL011851 UNNAMED UNNAMED 14
18510625 0 0
18510625 600 600
18510625 1200 1200
18510625 1800 1800
18510625 2100 2100
18510626 0 0
18510626 600 600
18510626 1200 1200
18510626 1800 1800
18510627 0 0
18510627 600 600
18510627 1200 1200
18510627 1800 1800
18510628 0 0
AL021851 AL021851 UNNAMED UNNAMED 1
18510705 1200 1200
AL031851 AL031851 UNNAMED UNNAMED 1
18510710 1200 1200
AL041851 AL041851 UNNAMED UNNAMED 49
18510816 0 0
18510816 600 600
18510816 1200 1200
18510816 1800 1800
18510817 0 0
18510817 600 600
18510817 1200 1200
18510817 1800 1800
18510818 0 0
18510818 600 600

 

[DanteAmor]

Run this macro

Sub Simple_copy()
  Dim c As Range
  For Each c In Range("E1:E" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants).Areas
    c.Offset(1).Resize(c.Value).Value = Right(Cells(c.Row, "A"), 4)
  Next
End Sub