Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
A function that will print out a function for OpenOffice spreadsheet or excel which can be used to concatenate cells with a common separator
################################################################################
# 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")
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment