Abbreviate the string

Copper Contributor

Hi,

 

I want to make the following abbreviations from the string , but not sure on the implementation

 

Shelaj29_0-1610524930611.png

 

Kindly help.

 

Thanks

3 Replies

@Shelaj29 

For such sample

image.png

it could be

=LET(name, B3, def, " - ",
   ndef, SEARCH(def,name),
   spfirst, SEARCH(" ",name),
   spsec, SEARCH(" ",name, ndef+3),
   first, UPPER(LEFT(name,2)),
   sec, IF(ISNUMBER(ndef), IF(ISNUMBER(spsec), MID(name,spsec+1,2), MID(name,ndef+3,2)), IF(ISNUMBER(spfirst),MID(name,spfirst+1,2),MID(name,3,1)  )),
   first & UPPER(sec))
Hey Sergei,

I guess It's not working in my excel version, any alternate formula which can work in 2016 version.

Thanks

@Shelaj29 

It could be

=UPPER(LEFT(B3,2)) &
 UPPER(
    IF(ISNUMBER(SEARCH(" - ",B3)),
       IF(ISNUMBER(SEARCH(" ",B3, SEARCH(" - ",B3)+3)),
          MID(B3,SEARCH(" ",B3, SEARCH(" - ",B3)+3)+1,2),
          MID(B3,SEARCH(" - ",B3)+3,2)
       ),
       IF(ISNUMBER(SEARCH(" ",B3)),
          MID(B3,SEARCH(" ",B3)+1,2),
          MID(B3,3,1)
      )
   )
)