Ah ok, so you can actually pull out the information in the header using cell references and add it to the data frame when reading each file, then supply a skip = 10 argument to read the main data table starting from row 11.
Like this (assuming the first cell in your screenshot is A1):
library(tidyverse)
library(readxl)
library(fs)
# path to directory containing excel files
excel_dir <- here::here("excel_dir/")
df_combined_long <-
# list all the excel file paths in the directory (assuming they are xlsx files not xls)
dir_ls(excel_dir, regexp = "[.]xlsx$") %>%
# read each file and combine together into single df
map_dfr(~{
series_id <- read_excel(path = .x, range = "B4", col_names = FALSE) %>% pull()
series_label <- read_excel(path = .x, range = "B5", col_names = FALSE) %>% pull()
industry <- read_excel(path = .x, range = "B6", col_names = FALSE) %>% pull()
product <- read_excel(path = .x, range = "B7", col_names = FALSE) %>% pull()
read_excel(.x, skip = 10) %>%
mutate(
series_id = series_id,
series_label = series_label,
industry = industry,
product = product
) %>%
select(series_id, series_label, industry, product, everything())
}) %>%
# convert to long format with a month and value column
pivot_longer(cols = Jan:Dec, names_to = "month", values_to = "value") %>%
# combine year and month columns into a single date column
unite(date, Year, month, sep = "-") %>%
# convert date column to R date format
mutate(date = parse_date(date, format = "%Y-%b"))