Abbreviate the string

%3CLINGO-SUB%20id%3D%22lingo-sub-2055777%22%20slang%3D%22en-US%22%3EAbbreviate%20the%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2055777%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20make%20the%20following%20abbreviations%20from%20the%20string%20%2C%20but%20not%20sure%20on%20the%20implementation%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Shelaj29_0-1610524930611.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F246035iE0166A8873F5D695%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Shelaj29_0-1610524930611.png%22%20alt%3D%22Shelaj29_0-1610524930611.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKindly%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2055777%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2055973%22%20slang%3D%22en-US%22%3ERe%3A%20Abbreviate%20the%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2055973%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F927232%22%20target%3D%22_blank%22%3E%40Shelaj29%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20283px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F246047i8C8EB30387DB6065%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eit%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLET(name%2C%20B3%2C%20def%2C%20%22%20-%20%22%2C%0A%20%20%20ndef%2C%20SEARCH(def%2Cname)%2C%0A%20%20%20spfirst%2C%20SEARCH(%22%20%22%2Cname)%2C%0A%20%20%20spsec%2C%20SEARCH(%22%20%22%2Cname%2C%20ndef%2B3)%2C%0A%20%20%20first%2C%20UPPER(LEFT(name%2C2))%2C%0A%20%20%20sec%2C%20IF(ISNUMBER(ndef)%2C%20IF(ISNUMBER(spsec)%2C%20MID(name%2Cspsec%2B1%2C2)%2C%20MID(name%2Cndef%2B3%2C2))%2C%20IF(ISNUMBER(spfirst)%2CMID(name%2Cspfirst%2B1%2C2)%2CMID(name%2C3%2C1)%20%20))%2C%0A%20%20%20first%20%26amp%3B%20UPPER(sec))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2057846%22%20slang%3D%22en-US%22%3ERe%3A%20Abbreviate%20the%20string%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2057846%22%20slang%3D%22en-US%22%3EHey%20Sergei%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20guess%20It's%20not%20working%20in%20my%20excel%20version%2C%20any%20alternate%20formula%20which%20can%20work%20in%202016%20version.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%3C%2FLINGO-BODY%3E
New 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)
      )
   )
)