Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

Extract initials from name while ignoring quotes

I'm using this bit of code to extract initals from names:

=IF(LEN(B21)-LEN(SUBSTITUTE(B21," ",""))=0,LEFT(B21,1),IF(LEN(B21)-LEN(SUBSTITUTE(B21," ",""))=1,LEFT(B21,1)&MID(B21,FIND(" ",B21)+1,1),LEFT(B21,1)&MID(B21,FIND(" ",B21)+1,1)&MID(B21,FIND(" ",B21,FIND(" ",B21)+1)+1,1)))

Open in new window


It works fine for...

John Stevens = JS
John Robert Stevens = JRS

However, it returns quotation marks...

John "Ray" Stevens = J"S
John Ray "Stevens" = JR"

Can the formula ignore quotation marks?
0
mcnuttlaw
Asked:
mcnuttlaw
2 Solutions
 
yawkey13Commented:
Replace each occurrence of "B21" with SUBSTITUTE(B21,"""","")

Here is the code for B1...
=IF(LEN(SUBSTITUTE(B1,"""",""))-LEN(SUBSTITUTE(SUBSTITUTE(B1,"""","")," ",""))=0,LEFT(SUBSTITUTE(B1,"""",""),1),IF(LEN(SUBSTITUTE(B1,"""",""))-LEN(SUBSTITUTE(SUBSTITUTE(B1,"""","")," ",""))=1,LEFT(SUBSTITUTE(B1,"""",""),1)&MID(SUBSTITUTE(B1,"""",""),FIND(" ",SUBSTITUTE(B1,"""",""))+1,1),LEFT(SUBSTITUTE(B1,"""",""),1)&MID(SUBSTITUTE(B1,"""",""),FIND(" ",SUBSTITUTE(B1,"""",""))+1,1)&MID(SUBSTITUTE(B1,"""",""),FIND(" ",SUBSTITUTE(B1,"""",""),FIND(" ",SUBSTITUTE(B1,"""",""))+1)+1,1)))
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

not pretty, but works:

=IF(LEN(SUBSTITUTE(B21,"""",""))-LEN(SUBSTITUTE(SUBSTITUTE(B21,"""","")," ",""))=0,LEFT(SUBSTITUTE(B21,"""",""),1),IF(LEN(SUBSTITUTE(B21,"""",""))-LEN(SUBSTITUTE(SUBSTITUTE(B21,"""","")," ",""))=1,LEFT(SUBSTITUTE(B21,"""",""),1)&MID(SUBSTITUTE(B21,"""",""),FIND(" ",SUBSTITUTE(B21,"""",""))+1,1),LEFT(SUBSTITUTE(B21,"""",""),1)&MID(SUBSTITUTE(B21,"""",""),FIND(" ",SUBSTITUTE(B21,"""",""))+1,1)&MID(SUBSTITUTE(B21,"""",""),FIND(" ",SUBSTITUTE(B21,"""",""),FIND(" ",SUBSTITUTE(B21,"""",""))+1)+1,1)))

Open in new window


Alternatively, put this into C21:

=SUBSTITUTE(B21,"""","")

Then change your original formula to evaluate C21 instead of B21:

=IF(LEN(C21)-LEN(SUBSTITUTE(C21," ",""))=0,LEFT(C21,1),IF(LEN(C21)-LEN(SUBSTITUTE(C21," ",""))=1,LEFT(C21,1)&MID(C21,FIND(" ",C21)+1,1),LEFT(C21,1)&MID(C21,FIND(" ",C21)+1,1)&MID(C21,FIND(" ",C21,FIND(" ",C21)+1)+1,1)))

Open in new window


cheers, teylyn
0
 
mcnuttlawAuthor Commented:
Thanks.  Makes for quite an ugly looking formula but it work.
0
 
Saqib Husain, SyedEngineerCommented:
A shortened formula....before Barry sees this

=IFERROR(LEFT(B1,1),"")&IFERROR(IF(MID(B1,FIND(" ",B1)+1,1)="""",MID(B1,FIND(" ",B1)+2,1),MID(B1,FIND(" ",B1)+1,1)),"")&IFERROR(IF(MID(B1,FIND(" ",B1,FIND(" ",B1)+1)+1,1)="""",MID(B1,FIND(" ",B1,FIND(" ",B1)+1)+2,1),MID(B1,FIND(" ",B1,FIND(" ",B1)+1)+1,1)),"")
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now