Forum Discussion

alvin199's avatar
alvin199
Icon for Helper III rankHelper III
4 years ago
Solved

Text Transformation - Big & small capital

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?

Need Help
  • v-henryk-mstf's avatar
    v-henryk-mstf
    4 years ago

    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.

7 Replies

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      alvin199
      Icon for Helper III rankHelper III
      4 years ago

      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's avatar
    VijayP
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      alvin199
      Icon for Helper III rankHelper III
      4 years ago

      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. 

  • VijayP's avatar
    VijayP
    Icon for Community Champion rankCommunity Champion
    4 years ago

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

    • alvin199's avatar
      alvin199
      Icon for Helper III rankHelper III
      4 years ago

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