Text Transformation - Big & small capital

[alvin199]

I have text data with mixture of big and small capital in a single column. The problem is some row starts with small capital letter like the first one on the After column. How can I maintain the first letter in small capital and transform to small capital after having big capital?

[v-henryk-mstf · Accepted solution]

Hi @alvin199 ,

According to your description, I did the test reference as follows:

col_change =
VAR FirstName =
    LEFT (
        SUBSTITUTE ( [col], ", ", "-" ),
        SEARCH ( "-", SUBSTITUTE ( [col], " ", "-" ) ) - 1
    )
VAR LastName =
    RIGHT (
        SUBSTITUTE ( [col], " ", "-" ),
        LEN ( SUBSTITUTE ( [col], " ", "-" ) )
            - SEARCH ( "-", SUBSTITUTE ( [col], " ", "-" ) )
    )
VAR F =
    UPPER ( LEFT ( FirstName, 1 ) )
        & LOWER ( RIGHT ( FirstName, LEN ( FirstName ) - 1 ) )
VAR L =
    UPPER ( LEFT ( LastName, 1 ) )
        & LOWER ( RIGHT ( LastName, LEN ( LastName ) - 1 ) )
RETURN
    F & " " & L

If the problem is still not resolved, please point it out. Looking forward to your feedback.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[alvin199]

Hi @v-henryk-mstf ,

May I know in FirstName & LastName, there is no "," in the col column.

I checked substitude function, this is the parameter: 

SUBSTITUTE(<text>, <old_text>, <new_text>, <instance_num>)

The "," is the second parameter so it is the old text that exit in the original col column.

Can you let me know which part of my understanding is incorrect?

[VijayP]

@alvin199 

Here you go ! File attached will help How I have done it in Power Query !

Share your Kudos !!

[VijayP]

So you want iPhone should be as it is and Zenith should be zEnith ??

[alvin199]

The After column is my data sample. The After column is my expectation. 

[VijayP]

@alvin199  This can be done in Power Query Editor , you can transform the column to Proper Format , i.e., iPhone to Iphone . Just right Click on column headers and use this option

[alvin199]

Hi @VijayP ,

I knew about this method. But this will change the first letter in small capital (i) become big capital. This does not match my expectation.