Trim first letter of each word in a string

[kfschaefer]

I am looking for the best approach to Trim the First letter of each text string to come up with an abbreviation.

Example.

Supply Chain and Mgmt.  = SCM

Electric Light & Power = ELP

Joe's Power Source Company =  JPSC

What functions should I use?  This is what I have so far, however, it is asking for a paran and =0, or =1,

IF(len([Org])-len(SUBSTITUTE([Org]," ",""))=0,left([Org],1),

if(len([Org])-len(SUBSTITUTE([Org]," ",""))=1, left([Org],1)

&mid([Org],find(" ",[Org])+1,1),left([Org],1)&mid([Org],find(" ",[Org])+1,1)&mid([Org],find(" ",[Org],find(" ",[Org])+1)+1,1)))

Thanks,

Karen

[BettinaEK]

Hi @kfschaefer 

An alternative, and more human-readable, solution using Power Query M is a single line of code (nested for readability):

Text.Remove(

   Text.Replace(

      Text.Proper(

         Text.Remove(

            Text.Trim(

               Text.Clean([Org])

            ),

         {"'"}) //remove those characters interpreted as delimiters in Text.Proper()

      ),

   " And "," "),

{"a".."z","0".."9","(",")"," ",",",".","&","£"})

The Power of M lies in it's human readability, which not only does not require encyclopaedic knowledge of ASCII or Unicode, but also automatically makes it much easier to customise -- all data sets are wonky in different and creative ways.

Your source data will drive the functions you use and the order in which you use them. Some of the steps in this example can be reordered or repeated to suit your data source.

Read more here: The Power of M... and don't forget the function reference library

BEK

redefining the box

[v-danhe-msft]

Hi @kfschaefer,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[v-danhe-msft · Accepted solution]

Hi @kfschaefer,

Based on my test, you could refer to below steps:

Sample data:

Split the [Text] column by space in query editor:

Extracted First Characters:

Apply it and create a calculated column:

Column = var a=IF(UNICODE('Table1'[Text.1])>=65&&UNICODE('Table1'[Text.1])<=90,[Text.1],BLANK())
        var b=IF(UNICODE('Table1'[Text.2])>=65&&UNICODE('Table1'[Text.2])<=90,[Text.2],BLANK())
        var c=IF(UNICODE('Table1'[Text.3])>=65&&UNICODE('Table1'[Text.3])<=90,[Text.3],BLANK())
        var d=IF(UNICODE('Table1'[Text.4])>=65&&UNICODE('Table1'[Text.4])<=90,[Text.4],BLANK())
        return CONCATENATE(a,CONCATENATE(b,CONCATENATE(c,d)))

Result:

You could also download the pbix to have a view:

https://www.dropbox.com/s/cfmvbv4kyl94thq/Trim%20first%20letter%20of%20each%20word%20in%20a%20string...

Regards,

Daniel He

[Anonymous]

In the table, if the name or the text string you want to format is in one column, I could think of one work around where you could extract the text before or after the delimiter/s. This will create separate columns for each word in the string. Then extract the first character for each word. Finally, combine the extracted first letters. It may seem like a long process, but it is actually easy to achieve.

Hope this helps!