Skip to contents

Conform names to spreadsheet workbook standards

Source: R/scrub_tabnames.R
scrub_tabnames.Rd

scrub_tabnames() preserves as much of the original tabnames as possible while ensuring each tabname is unique and contains only characters permissible as spreadsheet tab names

Usage

scrub_tabnames(
  tabnames,
  max_width = 31,
  truncate_side = c("right", "left", "center"),
  paste_side = c("right", "left"),
  sep = ".",
  pad = ".",
  quiet = FALSE
)

Arguments

tabnames

strings: character vector of names

max_width

integerish between 0 and 31: Default is 31 characters; longer names will cause Excel export to fail.

truncate_side

'right', 'left', or 'center': describes which side of a long tabname do you want to snip excess characters from

paste_side

'right' or 'left': describing which side of a tab name you want to paste characters to, if required

sep

chars: if pasting characters to a tab name, what character do you want to use to separate name and unique prefix/suffix; can be several characters like '...', but cannot be any of the forbidden characters described in details

pad

char: if pasting characters to a tab name, what character do you want to use to pad so numbers align, (e.g. '0' for '001' or '.' for '..1' if 3 digits of differentiation are necessary). Must be at least 1 character but no longer. Can be a space ' ', but cannot be any of the forbidden characters described in details

quiet

bool: to turn off warnings if you prefer with quiet = TRUE

Value

a character vector of names suitable for Excel or Librecalc tab/sheet names

Details

Replaces:

  • characters: /\: with: -

  • characters: ?*&$ with: #

  • characters: [< with: (

  • characters: ]> with: )

  • tabnames with the single quote or double quote: ' or " with a backtick `

  • the word 'history' in a tabname, with 'hist' while preserving capitalization

  • names greater than 31 characters with a truncated characters

  • duplicate sheetnames with numbered versions of the name to make each name within that group unique

References

Examples

some_tabnames <- c('\\:blue/:', 'red', 'gr?*een///////', '[]', '[orange]','', NA)
scrub_tabnames(some_tabnames)
#> ! Sheet names should not contain:  `\`
#> → Replacing forbidden character with:  `-`
#> ! Sheet names should not contain:  `/`
#> → Replacing forbidden character with:  `-`
#> ! Sheet names should not contain:  `:`
#> → Replacing forbidden character with:  `-`
#> ! Sheet names should not contain:  `*`
#> → Replacing forbidden character with:  `#`
#> ! Sheet names should not contain:  `?`
#> → Replacing forbidden character with:  `#`
#> ! Sheet names should not contain:  `[`
#> → Replacing forbidden character with:  `(`
#> ! Sheet names should not contain:  `]`
#> → Replacing forbidden character with:  `)`
#> ! Duplicate or empty names exist. Numbering within each group ... 
#> [1] "--blue--"       "red"            "gr##een-------" "()"            
#> [5] "(orange)"       ".1"             "NA"            
c('history', 'Entire History of the Universe') |> scrub_tabnames()
#> ! Sheet names should not contain:  `the word 'history'`
#> → Replacing forbidden character with:  `hist`
#> [1] "hist"                        "Entire Hist of the Universe"
c('\\/:[]?*', '\\?:*/[]', '~!@#$%^&()-_=+{}|;:,<.> ') |> scrub_tabnames()
#> ! Sheet names should not contain:  `\`
#> → Replacing forbidden character with:  `-`
#> ! Sheet names should not contain:  `/`
#> → Replacing forbidden character with:  `-`
#> ! Sheet names should not contain:  `:`
#> → Replacing forbidden character with:  `-`
#> ! Sheet names should not contain:  `*`
#> → Replacing forbidden character with:  `#`
#> ! Sheet names should not contain:  `?`
#> → Replacing forbidden character with:  `#`
#> ! Sheet names should not contain:  `?`
#> → Replacing forbidden character with:  `#`
#> ! Sheet names should not contain:  `[`
#> → Replacing forbidden character with:  `(`
#> ! Sheet names should not contain:  `]`
#> → Replacing forbidden character with:  `)`
#> ! Sheet names should not contain:  `<`
#> → Replacing forbidden character with:  `(`
#> ! Sheet names should not contain:  `>`
#> → Replacing forbidden character with:  `)`
#> [1] "---()##"                  "-#-#-()"                 
#> [3] "~!@#$%^#()-_=+{}|;-,(.) "
names(datasets::precip) |> scrub_tabnames(max_width = 5)
#> ! Duplicate or empty names exist. Numbering within each group ... 
#>  [1] "Mobil" "Junea" "Phoen" "Littl" "Los A" "Sacra" "San F" "Denve" "Hartf"
#> [10] "Wilmi" "Washi" "Jac.1" "Miami" "Atl.1" "Honol" "Boise" "Chica" "Peori"
#> [19] "India" "Des M" "Wichi" "Louis" "New O" "Por.1" "Balti" "Bosto" "Detro"
#> [28] "Sault" "Dulut" "Minne" "Jac.2" "Kansa" "St Lo" "Great" "Omaha" "Reno" 
#> [37] "Conco" "Atl.2" "Albuq" "Alban" "Buffa" "New Y" "Cha.1" "Ralei" "Bisma"
#> [46] "Cinci" "Cleve" "Col.1" "Oklah" "Por.2" "Phila" "Pitts" "Provi" "Col.2"
#> [55] "Sioux" "Memph" "Nashv" "Dalla" "El Pa" "Houst" "Salt " "Burli" "Norfo"
#> [64] "Richm" "Seatt" "Spoka" "Cha.2" "Milwa" "Cheye" "San J"
rep('', 15) |> scrub_tabnames(max_width = 0, sep = '..', pad = '.')
#> ! The minimum width must be greater than the number of characters required
#>         to differentiate possible duplicated tabnames plus the characters in `sep`.
#>         
#> Setting the tabname character width to 4.
#> ! Duplicate or empty names exist. Numbering within each group ... 
#>  [1] "...1" "...2" "...3" "...4" "...5" "...6" "...7" "...8" "...9" "..10"
#> [11] "..11" "..12" "..13" "..14" "..15"