1

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.

zx8754
  • 52,746
  • 12
  • 114
  • 209
Kalenji
  • 401
  • 2
  • 19
  • 42

1 Answers1

1

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

enter image description here

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.

zx8754
  • 52,746
  • 12
  • 114
  • 209
  • Parsed it to df no problem. Thanks again. Now I have another question if possible. How to do it with formulas are in Column B, C...I can not find any way to just import one column. – Kalenji Apr 06 '18 at 13:30
  • @Kalenji same method, there is an index variable showing which formula belongs to which cell. Invest some time understanding the structure of **sheet1** object. If your question is more complex/specific please ask a new question with example Excel file, and expected output. – zx8754 Apr 06 '18 at 13:33
  • 1
    I tried but having more then one column gives me an error in my big file. Have a look at [link](https://stackoverflow.com/questions/49753115/excel-import-to-data-frame-in-r-with-formulas) if possible. – Kalenji Apr 11 '18 at 13:25