A function that will print out a function for OpenOffice spreadsheet or excel which can be used to concatenate cells with a common separator

columncombiner.r

################################################################################
# A by-column concatenator for excel through R.
#
# Arguments:
# row - indicate the row you wish to concatenate.
# cell - the first cell containing the information you want to concatenate.
# lastcell - the last cell containing the information you want to concatenate.
#
# Usage: combine_excel(row = 1, cell = "A", lastcell = "D")
#
# When run with default settings, this will be printed to screen:
# 
#  =A1&"_"&B1&"_"&C1&"_"&D1 
#
# The user would copy and paste this into the input line of a new cell, and 
# excel will then concatenate cells A1:D1 using the common separator, "_"
#
# Example:
#
# We have 5 cells. A1 through D1 are filled and we want to concatenate them in
# cell E1. We would paste the above code as it is into the input line for E1.
# 
# A1  B1  C1  D1  E1
# 10  20  50  80  10_20_50_80
#
# Requirements: R
# 
# Limitations: Will only concatenate from A to ZZZ
################################################################################

combine_excel <- function(row = 1, cell = "A", lastcell = "D"){
  # doubles: AA to ZZ
  doubles <- paste(rep(toupper(letters), each = 26), toupper(letters), sep = "")
  excel <- c(toupper(letters), doubles)
  if(nchar(cell) | nchar(lastcell) > 2){
    # triples: AAA to ZZZ
    triples <- paste(rep(doubles, each = 26), toupper(letters), sep = "")
    excel <- c(excel, triples)
  }
  
  ends <- which(excel %in% c(cell, lastcell))
  if(length(ends) < 1){
    stop("Check your cell and lastcell arguments. They do not match any of the
         possible excel column names.\n")
  }

  else if(length(ends) < 2){
    stop("One of the arguments cell or lastcell did not match any of the
         possible excel column names. Please revise.\n")
  }
  
  endersgame <- paste(excel[ends[2]], row, sep="")
  excel <- excel[ends[1]:(ends[2]-1)]
  excelout <- c("=",paste(paste(excel, row, sep=""), "&\"_\"&", sep = ""), 
                endersgame)
  excelout <- paste(excelout, collapse="")
  cat("\n", excelout, "\n\n")
}