Extracting Initials from Multi-Word Last Names

[njpamdnc]

Dear Sir or Madam:

My name is Robert, and I am a middle school math teacher who is requesting your assistance with solving a frustrating problem. I have provided you with two scenarios for extracting initials from student names.

=IF(OR(COUNTIF(B101,"*SE*"),COUNTIF(B101,"*ESL*")),A101,"") gives me the result of "BP" for Budathoki, Pranik

=IF(OR(COUNTIF(B100,"*SE*"),COUNTIF(B100,"*ESL*")),A100,"") gives me the result of "BN" for Berrocales Noriega, Jacklyz

However, I want the result for Berrocales Noriega, Jacklyz to be "BJ". In other words, I want a formula to pull the first initial from the leftmost name in a field skipping through x amount of names until the formula is ready to pull the first letter of the first name. Any assistance you can provide will be greatly appreciated.

Sincerely,
Robert

 

[kweaver]

It would help if you used XL2BB to provide some examples of how the data is structured.

 

[lrobbo314]

A way using VBA.

Book1
	A	B
1	Name	Initials
2	Budathoki, Pranik	B.P.
3	Berrocales Noriega, Jacklyz	B.J.
Sheet4

Cell Formulas
Range	Formula
B2:B3	B2	=INITIALS(A2)

Function INITIALS(s As String)
Dim sName() As String: sName = Split(s, ", ")
INITIALS = Join(Array(Left(sName(0), 1), Left(sName(1), 1), ""), ".")
End Function

 

[sandy666]

you can try Power Query

Names	Initials
Budathoki, Pranik	BP
Berrocales Noriega, Jacklyz	BJ

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Initials = Table.AddColumn(Source, "Initials", each Text.Start(Text.Select([Names],{"A".."Z"}),1)&Text.End(Text.Select([Names],{"A".."Z"}),1))
in
    Initials

 

[kweaver]

Similar to Irobbo but a formula in B2:

=LEFT(A2,1)&"."&MID(A2,FIND(",",A2)+2,1)&"."

 

[lrobbo314]

Could use regular expressions as well.

Book1
	A	B	C
1	Name	Initials	RegExp
2	Budathoki, Pranik	B.P.	B.P.
3	Berrocales Noriega, Jacklyz	B.J.	B.J.
Sheet4

Cell Formulas
Range	Formula
B2:B3	B2	=INITIALS(A2)
C2:C3	C2	=IRX(A2)

Function IRX(s As String)
With CreateObject("VBScript.RegExp")
    .Pattern = "(^\w{1})(.*?,\s)(\w{1})(.+)"
    IRX = .Replace(s, "$1.$3.")
End With
End Function

 

[njpamdnc]

It would help if you used XL2BB to provide some examples of how the data is structured.

I'm sorry. I previously posted the wrong formulas. I meant to post the following formulas

=IF(COUNTIF(E101,"*,*"),LEFT(E101)&MID(E101,IFERROR(SEARCH(" ",E101),SEARCH(",",E101))+1,1),"") gives me the result of "BP" for Budathoki, Pranik

=IF(COUNTIF(E100,"*,*"),LEFT(E100)&MID(E100,IFERROR(SEARCH(" ",E100),SEARCH(",",E100))+1,1),"") gives me the result of "BN" for Berrocales Noriega, Jacklyz

However, I want the result for Berrocales Noriega, Jacklyz to be "BJ". In other words, I want a formula to pull the first initial from the leftmost name in a field skipping through x amount of names until the formula is ready to pull the first letter of the first name. Any assistance you can provide will be greatly appreciated.

 

[njpamdnc]

I'm sorry. I previously posted the wrong formulas. I meant to post the following formulas

=IF(COUNTIF(E101,"*,*"),LEFT(E101)&MID(E101,IFERROR(SEARCH(" ",E101),SEARCH(",",E101))+1,1),"") gives me the result of "BP" for Budathoki, Pranik

=IF(COUNTIF(E100,"*,*"),LEFT(E100)&MID(E100,IFERROR(SEARCH(" ",E100),SEARCH(",",E100))+1,1),"") gives me the result of "BN" for Berrocales Noriega, Jacklyz

However, I want the result for Berrocales Noriega, Jacklyz to be "BJ". In other words, I want a formula to pull the first initial from the leftmost name in a field skipping through x amount of names until the formula is ready to pull the first letter of the first name. Any assistance you can provide will be greatly appreciated.

A way using VBA.

Book1
	A	B
1	Name	Initials
2	Budathoki, Pranik	B.P.
3	Berrocales Noriega, Jacklyz	B.J.
Sheet4

Cell Formulas
Range	Formula
B2:B3	B2	=INITIALS(A2)

Function INITIALS(s As String)
Dim sName() As String: sName = Split(s, ", ")
INITIALS = Join(Array(Left(sName(0), 1), Left(sName(1), 1), ""), ".")
End Function

 

[njpamdnc]

you can try Power Query

Names	Initials
Budathoki, Pranik	BP
Berrocales Noriega, Jacklyz	BJ

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Initials = Table.AddColumn(Source, "Initials", each Text.Start(Text.Select([Names],{"A".."Z"}),1)&Text.End(Text.Select([Names],{"A".."Z"}),1))
in
    Initials

I'm sorry. I previously posted the wrong formulas. I meant to post the following formulas

=IF(COUNTIF(E101,"*,*"),LEFT(E101)&MID(E101,IFERROR(SEARCH(" ",E101),SEARCH(",",E101))+1,1),"") gives me the result of "BP" for Budathoki, Pranik

=IF(COUNTIF(E100,"*,*"),LEFT(E100)&MID(E100,IFERROR(SEARCH(" ",E100),SEARCH(",",E100))+1,1),"") gives me the result of "BN" for Berrocales Noriega, Jacklyz

However, I want the result for Berrocales Noriega, Jacklyz to be "BJ". In other words, I want a formula to pull the first initial from the leftmost name in a field skipping through x amount of names until the formula is ready to pull the first letter of the first name. Any assistance you can provide will be greatly appreciated.

 

[sandy666]

Any assistance you can provide will be greatly appreciated.

I don't understand
Post#4 contain your example (very poor example) and Power Query (Get&Transform) M code for that
what do you want more?
btw. there is no any worksheet formulas

I suggest you to update your profile about Excel version and OS