R function to limit characters of list element name?

user_15309749 0
Viewed 80

I have a list of elements. I want to export this list as an excel file with each element exporting as an individual sheet within the same excel file. Excel has a 31 character limit for sheet names. Thus the below export will not work.

Is there a way to limit the characters of a list using a function? For example, cut off all element names at 30 characters?

employee <- c('John Doe','Peter Gynn','Jolie Hope')
salary <- c(21000, 23400, 26800)
startdate <- as.Date(c('2010-11-1','2008-3-25','2007-3-14'))

test1 <- list(employee, salary, startdate)

names(test1)[1] <- "employee of the month 2021 to 2022"
names(test1)[2] <- "salary of the best employees of test company"
names(test1)[3] <- "startdate"

write.xlsx(test1, file = "testlist.xlsx")

TIA

export r excel
2 Answers

Function substr will work.

> substr("salary of the best employees of test company", start = 1, stop = 30)
[1] "salary of the best employees o"
user_3229120

In this approach you can save space by defining a stopwords list, so there is more space for meaningful sheetnames. The example includes the change of your names in your test1 list. Either keep the words in tact, or cut of words "halfway".

library(stringi)

# remove words without much meaning, add what you like
stopwords <- c("of", "the", "to")

names(test1) <- unlist(lapply(names(test1), function (x){
  x <- stri_extract_all_words(x)[[1]]
  x <- paste(x[!x %in% stopwords], collapse = " ")
  # strwrap(x, width = 30)[1] # keep words in tact
  substr(x, start = 1, stop = 30)
}))

names(test1)

# strwrap uncommented
# [1] "employee month 2021 2022"   "salary best employees test" "startdate"

# substr uncommented
# [1] "employee month 2021 2022"        "salary best employees test com" "startdate" 

# compare that to the outcome without removing stopwords
# [1] "employee of the month 2021 to " "salary of the best employees o" "startdate"
user_104157490
Related