The tidyr package has great tools for data manipulation tasks such as this. We can achieve this by using the extract() and separate() functions that it contains.
library(tidyr)
data <- tibble(metric1 = c("Total Revenue 112 110 108 100 94",
"Total Revenue 534 523 514 500 485",
"Cost of Revenue 73 72 71 66 62",
"Cost of Revenue 403 394 385 373 361",
"Gross Profit 38 37 37 34 32",
"Gross Profit 131 129 129 126 124"))
data <- data %>%
extract(col = metric1, into = c("Metric", "Numbers"), regex = "(\\D+)(.+)") %>%
separate(col = Numbers, into = c("Year1", "Year2", "Year3", "Year4", "Year5"), sep = " ")
print(data)
#> # A tibble: 6 x 6
#> Metric Year1 Year2 Year3 Year4 Year5
#> <chr> <chr> <chr> <chr> <chr> <chr>
#> 1 "Total Revenue " 112 110 108 100 94
#> 2 "Total Revenue " 534 523 514 500 485
#> 3 "Cost of Revenue " 73 72 71 66 62
#> 4 "Cost of Revenue " 403 394 385 373 361
#> 5 "Gross Profit " 38 37 37 34 32
#> 6 "Gross Profit " 131 129 129 126 124
Created on 2020-05-29 by the reprex package (v0.3.0)