How to capitalize the first character of each word in a string using Tableau?

user_13254554 0
Viewed 7209

I have a columns named 'ENTIDAD_FEDEREATIVA' which contains the name of each state of Mexico in upper case and looks like this:

enter image description here

What I want to do is to pass from upper to lower each character of each string in that column except the first character, therefore, the column should look like this:

enter image description here

I've been trying to do something like this UPPER(LEFT([ENTIDAD_FEDERATIVA], 1)) + MID([ENTIDAD_FEDERATIVA], 2) but I got this error: syntax error operand missing. If anyone has a better idea I would really appreciate your help. Thanks in advance.

tableau-api
3 Answers

Unfortunately there is no function for that in Tableau (similar to =Proper() in excel) however we can do a workaround.

Example data:

enter image description here

If we only have one word in each string (One word only), we can use:

UPPER(LEFT([ENTIDAD_FEDERATIVA],1)) + LOWER(MID([ENTIDAD_FEDERATIVA], 2, LEN([ENTIDAD_FEDERATIVA]) -1))

But since we have more words we need the split the string and perform the above logic to each word.

We start with the strings that have most words.

Third word will have the formula (split string and take the third occurrence SPLIT([ENTIDAD_FEDERATIVA], " ",3):

UPPER(LEFT(SPLIT([ENTIDAD_FEDERATIVA], " ",3),1)) + LOWER(MID(SPLIT([ENTIDAD_FEDERATIVA], " ",3), 2, LEN(SPLIT([ENTIDAD_FEDERATIVA], " ",3)) -1))

Everywhere when no third word is found it will return a "Null" value. Therefore we wrap the formula in IFNULL(<expr1>,<expr2>)

Where <expr1> will be all the words (first word + second word + third word) since we want to return the full string. For the <expr2> aka "Null" values, we do the same, but now we go for Second word.

Second word formula (we change which word we should return in the split function, SPLIT([ENTIDAD_FEDERATIVA], " ",2):

UPPER(LEFT(SPLIT([ENTIDAD_FEDERATIVA], " ",2),1)) + LOWER(MID(SPLIT([ENTIDAD_FEDERATIVA], " ",2), 2, LEN(SPLIT([ENTIDAD_FEDERATIVA], " ",2)) -1)),

With this logic we can extend this to 7 or 10 or how many words we might have in a "cell".

For three words the complete calculated field (Calculation4) will look like:

// Third word:
IFNULL(
UPPER(LEFT(SPLIT([ENTIDAD_FEDERATIVA], " ",1),1)) + LOWER(MID(SPLIT([ENTIDAD_FEDERATIVA], " ",1), 2, LEN(SPLIT([ENTIDAD_FEDERATIVA], " ",1)) -1))
+ " " + 
UPPER(LEFT(SPLIT([ENTIDAD_FEDERATIVA], " ",2),1)) + LOWER(MID(SPLIT([ENTIDAD_FEDERATIVA], " ",2), 2, LEN(SPLIT([ENTIDAD_FEDERATIVA], " ",2)) -1))
+ " " + 
UPPER(LEFT(SPLIT([ENTIDAD_FEDERATIVA], " ",3),1)) + LOWER(MID(SPLIT([ENTIDAD_FEDERATIVA], " ",3), 2, LEN(SPLIT([ENTIDAD_FEDERATIVA], " ",3)) -1)),

// Second word:
IFNULL(
UPPER(LEFT(SPLIT([ENTIDAD_FEDERATIVA], " ",1),1)) + LOWER(MID(SPLIT([ENTIDAD_FEDERATIVA], " ",1), 2, LEN(SPLIT([ENTIDAD_FEDERATIVA], " ",1)) -1))
+ " " + 
UPPER(LEFT(SPLIT([ENTIDAD_FEDERATIVA], " ",2),1)) + LOWER(MID(SPLIT([ENTIDAD_FEDERATIVA], " ",2), 2, LEN(SPLIT([ENTIDAD_FEDERATIVA], " ",2)) -1)),

// First word:
UPPER(LEFT(SPLIT([ENTIDAD_FEDERATIVA], " ",1),1)) + LOWER(MID(SPLIT([ENTIDAD_FEDERATIVA], " ",1), 2, LEN(SPLIT([ENTIDAD_FEDERATIVA], " ",1)) -1))))
user_45525290

If your string has up to 1 space in it, this will work.

The spaces around [column_name] make it easier to double click to replace with your column name.

IF  CONTAINS( [column_name] , " ")

THEN
    UPPER( LEFT( [column_name] , 1) ) 

    +   LOWER( LEFT(
                    RIGHT( [column_name] 
                          , LEN( [column_name] ) - 1
                          )
                    ,FIND( [column_name] ," ") - 2
                    )
              )
    + " " 
    + UPPER(
            LEFT(
                RIGHT( [column_name] 
                      , LEN( [column_name] )
                      - FIND( [column_name] ," ")
                      )
                , 1
                )
            )
    + LOWER(
            RIGHT( [column_name] 
                , LEN( [column_name] )
                  - FIND( [column_name] ," ") - 1
               )
            )
ELSE
    UPPER( LEFT( [column_name] , 1) ) 

    +   LOWER( RIGHT( [column_name] 
                     , LEN( [column_name] ) - 1
                     )
             )

END
user_62458970

I spilt up my main field using the space delimiter and then combined all split field using the below formula.

upper(left([Comments - Split 1],1))+ lower(right([Comments - Split 1],len([Comments - Split 1])-1)) + ' ' +
upper(left([Comments - Split 2],1))+ lower(right([Comments - Split 2],len([Comments - Split 2])-1)) + ' ' +
upper(left([Comments - Split 3],1))+ lower(right([Comments - Split 3],len([Comments - Split 3])-1)) + ' ' +
upper(left([Comments - Split 4],1))+ lower(right([Comments - Split 4],len([Comments - Split 4])-1)) + ' ' +
upper(left([Comments - Split 5],1))+ lower(right([Comments - Split 5],len([Comments - Split 5])-1))

However, this solution is not scalable, as we do not know how many words may occur in a sentence.

user_160477880
Related