cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Community Support Team
Community Support Team

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.
3 REPLIES 3
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!

Community Support Team
Community Support Team

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.
Community Support Team
Community Support Team

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.