Function to abbreviate name

[S P P]

Goodnight!

Function that eliminates from the abbreviation the "de" ,"do" ,"dos , "da" ,"das"

I need a function that eliminates from the abbreviation the "de" ,"do" ,"dos , "da" ,"das"

 

[AlanSidman]

Not sure what you want. How about showing us what a solution looks like for your example.

 

[shrivallabha]

If you have Office 365 then you can try below formula:
=UPPER(TEXTJOIN("",TRUE,LEFT(FILTER(FILTERXML("<t><d>"&SUBSTITUTE(A2," ","</d><d>")&"</d></t>","//d"),ISNA(MATCH(FILTERXML("<t><d>"&SUBSTITUTE(A2," ","</d><d>")&"</d></t>","//d"),$F$2:$F$6,0)),""))))

We first split all words and then test the against predefined list of items to exclude such as "da", "de" etc.

Please see attached file for implementation.

 

[S P P]

shrivallabha
Good Morning!

Thank you for your help

It has to be for several offices, so it has to be a function or a macro.

 

[shrivallabha]

shrivallabha
Good Morning!

Thank you for your help

It has to be for several offices, so it has to be a function or a macro.

If it is within organization then it should not be really an issue if you all have the same version i.e. office 365. However, if variations are expected then a VBA based UDF can be introduced. Is that what you are looking for?

 

[S P P]

It has to serve in any office

Office 2007,2010,2013

I made a formula to replace, and I'm using a function to shorten it.

My intention was one function only.

Replace and abbreviate.

 

[p45cal]

In the attached an Abbrev function:

Function Abbrev(LongName)
Dim ShortWordsToGo, wrds, i
ShortWordsToGo = Array("DOS", "DAS", "DO", "DE", "DA")
wrds = Split(Application.Trim(UCase(LongName)))
For i = LBound(wrds) To UBound(wrds)
  If Not IsError(Application.Match(wrds(i), ShortWordsToGo, 0)) Then wrds(i) = "" Else wrds(i) = Left(wrds(i), 1)
Next i
Abbrev = Join(wrds, "")
End Function

Used in cells B9:B11
eg =Abbrev(A2)

 

[S P P]

p45cal

Good evening!

Isto que eu estava precisando elimina uma formúla e serve para todos.

Thanks a lot for the help.