Data import and text to column (when in excel A1 = 1+1+1 and 3 is visible)

Question

Let's say we have the excel file and in A1 we have formula = 1+1+1. So excel shows 3 however you can do 'Text to Columns' and value 1 will be in A1, B1 & C1.

Is there anything we can do in R after data import (I use read.xlsx) to get the same result (1,1,1 as oppose to just 3)?

Just to confirm, the original df is 1x1 with value 3 and the desired outcome is df 1x3 with 1 in all 3 cells.

Answer 1

Not the full answer, but should get you started. If we have this example file:

We can use openxlsx package to get the formula:

library(openxlsx)

# read as workbook object
wb <- loadWorkbook("test.xlsx")

# get the 1st sheet
sheet1 <- wb$worksheets[[1]]

# get the cell formulas
sheet1$sheet_data$f
# [1] NA             "<f>1+1+1</f>" NA

Here, we can see the formula, the second item in sheet1$sheet_data$f. Meaning 1st column 2nd row. The rest of the job is to parse it into data.frame columns.