Working with Data: All the calculations you need t
  • Introduction
  • What is this book?
  • How to Use this Book
  • Document Notes
  • Authors' Thanks
  • Strings
    • Concatenate
    • Split
    • Length
    • Slice
    • Trim
    • Contains
    • Match
    • Replace
    • Upper/Lower Case
  • Numbers
    • Sum
    • Average
    • Roll Ups
    • Round
    • Floor
    • Power
    • Square Root
    • Absolute
  • Dates
    • Date Difference
    • Age
  • Logical
    • Logical Operators
    • If
    • Case / Switch
    • Is Null/Empty
  • General
    • Count
    • Filter
    • Sort
    • Lookup/Join
    • Variables
    • Convert Type
  • Appendix
    • DateTime Parts
    • Helpful Resources
Powered by GitBook
On this page
  • Excel
  • Tableau
  • Alteryx
  • OrgVue
  • tSQL
  • Python

Was this helpful?

  1. Strings

Upper/Lower Case

Converts a string to all uppercase, all lowercase, or proper case (where the first letter of each word is capitalised).

Excel

  • Upper: =~UPPER!~(‹string›)

  • Lower: =~LOWER!~(‹string›)

  • Proper: =~PROPER!~(‹string›)

=UPPER(A1) // "Main1234" -> "MAIN1234"
=LOWER(A2) // "Id0001" -> "id0001"
=PROPER(A3) // "DIV HR OPS" -> "Div Hr Ops"

Tableau

  • Upper: ~UPPER!~(‹string›)

  • Lower: ~LOWER!~(‹string›)

UPPER([Cost Centre]) // "Main1234" -> "MAIN1234"
LOWER([ID code]) // "Id0001" -> "id0001"

NB. Proper/Title case is not available as a standard calculation in Tableau. The only workaround (other than manipulating the data at source, which is preferable) is to split out the value using space characters as delimiters, slice the first character from each string and convert it to upper case, then recombine all string fragments:

IFNULL(UPPER(LEFT([Name],1)) + LOWER(MID([Name],2,FINDNTH([Name]," ",1)-1))
+ UPPER(MID([Name],FINDNTH([Name]," ",1)+1,1)) + LOWER(MID([Name],FINDNTH([Name]," ",1)+2,LEN([Name]))),"")

Alteryx

Upper: ~UPPERCASE!~(‹string›) Lower: ~LOWERCASE!~(‹string›) Proper: ~TITLECASE!~(‹string›)

UPPERCASE([Cost Centre]) // "Main1234" -> "MAIN1234"
LOWERCASE([ID code]) // "Id0001" -> "id0001"
TITLECASE([Business Unit]) // "DIV HR OPS" -> "Div Hr Ops"

OrgVue

  • Upper: ‹string›.value.~toUpperCase()!~

  • Lower: ‹string›.value.~toLowerCase()!~

node.costcentre.value.toUpperCase() // "Main1234" -> "MAIN1234"
node.id.value.toLowerCase() // "Id0001" -> "id0001"

NB. Proper/Title case is not available as a standard calculation in OrgVue. The only workaround (other than manipulating the data at source, which is preferable) is to split out the value using space characters as delimiters, slice the first character from each string and convert it to upper case, then recombine all string fragments.

array(node.name.value.split(' '))
    .map(i=>[
            i.value.slice(0,1).toUpperCase(),
            i.value.slice(1).toLowerCase()
        ].join(''))
    .join(' ')

tSQL

  • Upper: ~UPPER!~(‹string›) OR ~UCASE!~(‹string›)

  • Lower: ~LOWER!~(‹string›) OR ~LCASE!~(‹string›)

SELECT UPPER(CostCentre) AS CostCode
FROM ProductsData
SELECT LOWER(ID) AS Identifier
FROM OrdersData

Python

  • Upper: ‹string›.~upper()!~

  • Lower: ‹string›.~lower()!~

  • Proper: ‹string›.~title()!~

costCentre.upper() # "Main1234" -> "MAIN1234"
id.lower() # "Id0001" -> "id0001"
businessUnit.title() # "DIV HR OPS" -> "Div Hr Ops"
PreviousReplaceNextNumbers

Last updated 5 years ago

Was this helpful?

NB. The above examples assume variables have been declared for costCentre, id and businessUnit. For more information, go to .

General > Variables