Jan 13 2021 12:03 AM
Hi,
I want to make the following abbreviations from the string , but not sure on the implementation
Kindly help.
Thanks
Jan 13 2021 01:43 AM
For such sample
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))
Jan 13 2021 10:25 AM
Jan 14 2021 03:32 AM
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)
)
)
)