cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kfschaefer Member
Member

Trim first letter of each word in a string

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,Capture.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-danhe-msft
Microsoft

Re: Trim first letter of each word in a string

Hi @kfschaefer,

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

Sample data:

1.PNG

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

2.PNG

Extracted First Characters:

3.PNG

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:

1.PNG

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

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Highlighted
jenelrosales Regular Visitor
Regular Visitor

Re: Trim first letter of each word in a string

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!

Microsoft v-danhe-msft
Microsoft

Re: Trim first letter of each word in a string

Hi @kfschaefer,

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

Sample data:

1.PNG

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

2.PNG

Extracted First Characters:

3.PNG

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:

1.PNG

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

 

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Microsoft v-danhe-msft
Microsoft

Re: Trim first letter of each word in a string

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

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BettinaEK New Member
New Member

Re: Trim first letter of each word in a string

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors