Simple Copy

rcsberg

New Member
Joined
Oct 9, 2019
Messages
2
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!
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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.
 
Upvote 0
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
 
Upvote 0
Run this macro

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top