read_excel and NA values

Using one of readxl example files for reproducible example, you can open its location by running browseURL(dirname(readxl_example("type-me.xlsx"))), though the sheet looks like this:

library(readxl) library(dplyr) xlsx <- readxl_example("type-me.xlsx") # open file location explorer: # browseURL(dirname(readxl_example("type-me.xlsx"))) # by default blank cells are treated as missing data, note the single <NA>: df <- read_excel(xlsx, sheet = "text_coercion") %>% head(n = 2) df #> # A tibble: 2 × 2 #> text explanation #> <chr> <chr> #> 1 <NA> "empty" #> 2 cabbage "\"cabbage\"" # add "empty" to na vector, note 2 <NA> values: df <- readxl::read_excel(xlsx, sheet = "text_coercion", na = c("", "empty")) %>% head(n = 2) df #> # A tibble: 2 × 2 #> text explanation #> <chr> <chr> #> 1 <NA> <NA> #> 2 cabbage "\"cabbage\"" # to replace all(!) NA values with "" df[is.na(df)] <- "" df #> # A tibble: 2 × 2 #> text explanation #> <chr> <chr> #> 1 "" "" #> 2 "cabbage" "\"cabbage\"" 

^{Created on 2023-01-26 with reprex v2.0.2}

Note from your screenshot: you have column names in the first row of your dataframe, this breaks data type detection (everything is chr) and you should deal with that first; at that point data[is.na(data)] <- "" will no longer work as you can not write strings to numerical columns. And it's perfectly fine.