How to wrap values of a row into new line at specific character in R

Question

I have a sample dataframe with values:

data <- structure(list(A = c("Date)", "Values"), B = c("2023-04-03", "Heat Capacity\nSpecific Heat Capacity\nHeat Index" )), row.names = c(NA, -2L), class = c("tbl_df", "tbl", "data.frame" ))

Here the 2nd row of last column extends to the right. I want the each values to be in new line without adding new rows.

My desired output:

The above table is created using gt():

library(gt)
data %>%   
mutate(b = str_replace_all(b, "\n", "<br>")) %>%    
gt() %>%    
fmt_markdown(columns = TRUE)

But this is not useful as my final desired output I want is in xlsx file and using gt() wouldn't work. Is it possible to do anything with dplyr and stringr to achieve this similar result

Answer 1

You can use package gt to format presentation quality tables.
See this introduction to gt.

Here is the code, output omitted.

suppressPackageStartupMessages({
  library(tidyverse)
  library(gt)
})

data <- structure(list(
  A = c("Date)", "Values"), 
  B = c("2023-04-03", "Heat Capacity\nSpecific Heat Capacity\nHeat Index" )), 
  row.names = c(NA, -2L), class = c("tbl_df", "tbl", "data.frame" ))

data %>%
  mutate(B = gsub("\\n", "<br>", B)) %>%
  gt() %>%
  fmt_markdown(columns = everything())

^{Created on 2023-08-05 with reprex v2.0.2}

---

Edit

You can export to a HTML file and open the file in Excel:

• Microsoft,
• StackOverflow.

data %>%
  mutate(B = gsub("\\n", "<br>", B)) %>%
  gt() %>%
  fmt_markdown(columns = everything()) %>%
  as_raw_html() %>%
  writeLines(con = "~/Temp/so.html")