# Load required libraries
library(readxl)
library(openxlsx)
# Function to extract values from column "C" and include all original columns
extract_values <- function(start_word, stop_word, data) {
result <- character(length(start_word))
for (i in seq_along(start_word)) {
if (!is.na(start_word[i]) & !is.na(stop_word[i]) & start_word[i] != "" & stop_word[i] != "") {
start_pos <- grep(start_word[i], data$A)
stop_pos <- grep(stop_word[i], data$B)
if (length(start_pos) > 0 & length(stop_pos) > 0 & stop_pos > start_pos) {
result[i] <- substr(data$C[start_pos], start = nchar(as.character(start_word[i])) + 1, stop = nchar(as.character(data$C[start_pos])) - nchar(as.character(stop_word[i])) - 2)
} else {
result[i] <- NA
}
} else if (is.na(start_word[i]) & !is.na(stop_word[i]) & stop_word[i] != "") {
# Get all words before stop word
stop_pos <- grep(stop_word[i], data$B)
result[i] <- substr(as.character(data$C[stop_pos]), start = 1, stop = nchar(as.character(data$C[stop_pos])) - 1)
} else if (!is.na(start_word[i]) & is.na(stop_word[i]) & start_word[i] != "") {
# Get all words after start word
start_pos <- grep(start_word[i], data$A)
result[i] <- substr(as.character(data$C[start_pos]), start = nchar(as.character(start_word[i])) + 1, stop = nchar(as.character(data$C[start_pos])))
}
}
# Create a data frame with all original columns and the extracted values
result_df <- cbind(data, Extracted_Values = result)
return(result_df)
}
# Read data from Excel file
data <- read_excel("path_to_your_excel_file.xlsx")
# Example usage
start_words <- data$A
stop_words <- data$B
values <- extract_values(start_words, stop_words, data)
# Write data to a new Excel file
write.xlsx(values, "output_file.xlsx")
