How to retrieve a variable value inside a text string between quotes

Question

I need to export a table from R to Excel. One of the Excel columns has to be a hyperlink. So I'm adding a new column to build a string text using the "=HYPERLINK" function so Excel will interpret it as a hyperlink.

Excel HYPERLINK function:

=HYPERLINK(link_location, [friendly_name])

To construct my hyperlink, I need to use 2 variables from the original table.

The "Id" variable to build the URL for the "link_location"
The "CaseNumber" variable to be displayed as "friendly_name"

Based on my example:

=HYPERLINK(Id, [CaseNumber])

So first I tried:

Import %>%
    select (Id, CaseNumber) %>%
    mutate(CaseLink = glue::glue("=HYPERLINK(https://abc.xyz/{Id}, {CaseNumber})"))

Output example:

Id	CaseNumber	CaseLink
5004V000000000000A	00000001	=HYPERLINK(https://abc.xyz/5004V000000000000A, 00000001)

But it did not work because Excel requires the "link_location" URL to be under double quotes, like:

=HYPERLINK("https://abc.xyz/5004V000000000000A", 00000001)

So I used the function glue::double_quote to have the "link_location" URL string between double quotes:

Import %>%
    select (Id, CaseNumber) %>%
    mutate(CaseLink = glue::glue('=HYPERLINK({glue::glue_collapse(glue::double_quote("https://abc.xyz/{Id}"))}, {CaseNumber})'))

But it also did not work because it is no longer retrieving the "Id" info, but printing it as text "{Id}" as part of the string!

Output example:

Id	CaseNumber	CaseLink
5004V000000000000A	00000001	=HYPERLINK("https://abc.xyz/{Id}", 00000001)

But what I needed is:

Id	CaseNumber	CaseLink
5004V000000000000A	00000001	=HYPERLINK("https://abc.xyz/5004V000000000000A", 00000001)

Anyone could help me to correct it to accomplish such a result? It doesn't need to be using the glue package or using the HYPERLINK function from Excel. Other options and ideas are also welcome.

score 0 · Accepted Answer · answered Oct 18 '22 at 17:59

0

Works with stringr::str_glue:

import %>%
    mutate(CaseLink = str_glue('=HYPERLINK("https://abc.xyz/{Id}", {CaseNumber})'))

# A tibble: 1 x 3
  id                 case_num CaseLink                                                  
  <chr>              <chr>    <glue>                                                    
1 5004V000000000000A 00000001 =HYPERLINK("https://abc.xyz/5004V000000000000A", 00000001)

answered Oct 18 '22 at 17:59

Abigail

370
1
11

Additional notes in case someone is facing the same problem to have a formula working on an exported Excel file. Even though the above answer solved how to build the formula, Excel was still not interpreting the column as a formula, so what I did was convert the column to "xl_formula" class using: `class(df$CaseLink) <- 'xl_formula'`. Now when exported to Excel the cells are interpreted as formulas, and the link works. Thanks @Abigail for unblocking me and please let me know if there is a better solution. – Leonardo Lira Oct 19 '22 at 12:56

How to retrieve a variable value inside a text string between quotes

1 Answers1