Initials in a multiple-word cell

[Andresleo47]

Hi Mr. Excel fellas! Hoping you're doing well.

The following are some of the values that I have in a single column:

332241
4321 - P1 W2 John Smith
Balance for Alvin James Fox
12345 - P10 W4 Carl Sagan
12345

Now, I need to put the initials of the names in the next column, so, the output would be

-
JS
AJF
CS
-

Any thoughts?

This is something I would truly appreciate!

Thanks so much,

AV

 

[Andresleo47]

Hi Peter, thanks for the code. It works pretty well for now (Post # 28). I truly appreciate it! I will try Sandy's solution later. Thanks to the 3 of you!! I really appreciate all your proposals!

 

[sandy666]

You are welcome

 

[sandy666]

"You are welcome" is not a solution
if you want to mark post as solution just mark post#29

 

[Andresleo47]

True, thanks Sandy...3 very good solutions for each one of you!

 

[sandy666]

Thanks for the feedback
Have a nice day

 

[Joe4]

if you want to mark post as solution just mark post#29

When there are multiple solutions offered, please do not presume to know which one the user is going to select as the accepted solution.
They are free to select whichever one they see fit, free of any pressure from responders.

 

[Dossfm0q]

Formula

HHH.xlsx
	A	B
1
2	2310672 - P7 W2 Jean Sebestien Pelletier.	JSP
3	3310678 - P7 W2 Natasha Trzebinski.	NT
4	3227881 - P1 W3 Jordan Houghton	JH
5	3326480 - P8 W3 Evaughn Heath	EH
6	3344673 - P9 W4 Katelyn Young	KY
7	3264657 - P4W2 Josee Robitaille	JR
8	3228771 - P1 W3 Jean-Sebastien Giroux	JG
9	AA3181227
10	45848
11	3012404 - P04 W4 joan McKinley	JM
12	3030481 - P05 W1 Valencia Del-hierro	VD
13	2954286 - Samantha Veltkamp	SV
14	2981640 - P04 W4 Ashton Aubichon-Corrigal	AA
15	3121502 - P09 W2 Myla Sky Stanley Osuthorpe	MSSO
16	3166413 - P10 W4 Sandra NEUFELD	SN
17	3187164 - P12 W3 George etzerza	GE
18	CREDIT MEMO EQUITABLE LIFE OF CANADA
19	88316376 - P9 W3 Jackie (Jadwiga) Jakinik.	JJ
20	3521802 - P10 W3 Judit Dell'Anno.	JD
21	81745374 - P6 W1 Jasbir Kaur	JK
22	88211243 - P6 W2 Frank (huai yang) Sun.	FS
23	88218954 - P6 W1 Ashok Gangwar.	AG
24	PT-P Chq 28722405 -
25	6911380-Bugg Sondra	BS
26	88304744 - P8W2 87`Luciel Lamch	LL
27	6911380-Hofer Maria	HM
28
29
ورقة1

Cell Formulas
Range	Formula
B2:B29	B2	=TEXTJOIN("",1,IF(ISNUMBER(MATCH(CODE(MID(PROPER(LOWER(IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(MID(A2,FIND("W",A2,1),2))=FALSE,MID(A2,FIND(MID(A2,FIND("W",A2,1),2),A2)+3,99),IF(FIND("-",A2,1)<>0,MID(A2,AGGREGATE(14,6,FIND("|",SUBSTITUTE("-"&A2&"-","-","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))+1)))),1),99),"X")),"-",""),"'",""),LEFT(RIGHT(A2,LEN(A2)-FIND("(",A2,1)+1),FIND(")",RIGHT(A2,LEN(A2)-FIND("(",A2,1)+1))),""),SUBSTITUTE(SUBSTITUTE(IF(ISERROR(MID(A2,FIND("W",A2,1),2))=FALSE,MID(A2,FIND(MID(A2,FIND("W",A2,1),2),A2)+3,99),IF(FIND("-",A2,1)<>0,MID(A2,AGGREGATE(14,6,FIND("|",SUBSTITUTE("-"&A2&"-","-","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))+1)))),1),99),"X")),"-",""),"'","")))),ROW(INDIRECT("1:"&LEN(PROPER(LOWER(IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(MID(A2,FIND("W",A2,1),2))=FALSE,MID(A2,FIND(MID(A2,FIND("W",A2,1),2),A2)+3,99),IF(FIND("-",A2,1)<>0,MID(A2,AGGREGATE(14,6,FIND("|",SUBSTITUTE("-"&A2&"-","-","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))+1)))),1),99),"X")),"-",""),"'",""),LEFT(RIGHT(A2,LEN(A2)-FIND("(",A2,1)+1),FIND(")",RIGHT(A2,LEN(A2)-FIND("(",A2,1)+1))),""),SUBSTITUTE(SUBSTITUTE(IF(ISERROR(MID(A2,FIND("W",A2,1),2))=FALSE,MID(A2,FIND(MID(A2,FIND("W",A2,1),2),A2)+3,99),IF(FIND("-",A2,1)<>0,MID(A2,AGGREGATE(14,6,FIND("|",SUBSTITUTE("-"&A2&"-","-","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))+1)))),1),99),"X")),"-",""),"'",""))))))),1)),ROW(INDIRECT("65:90")),0)),MID(PROPER(LOWER(IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(MID(A2,FIND("W",A2,1),2))=FALSE,MID(A2,FIND(MID(A2,FIND("W",A2,1),2),A2)+3,99),IF(FIND("-",A2,1)<>0,MID(A2,AGGREGATE(14,6,FIND("|",SUBSTITUTE("-"&A2&"-","-","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))+1)))),1),99),"X")),"-",""),"'",""),LEFT(RIGHT(A2,LEN(A2)-FIND("(",A2,1)+1),FIND(")",RIGHT(A2,LEN(A2)-FIND("(",A2,1)+1))),""),SUBSTITUTE(SUBSTITUTE(IF(ISERROR(MID(A2,FIND("W",A2,1),2))=FALSE,MID(A2,FIND(MID(A2,FIND("W",A2,1),2),A2)+3,99),IF(FIND("-",A2,1)<>0,MID(A2,AGGREGATE(14,6,FIND("|",SUBSTITUTE("-"&A2&"-","-","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))+1)))),1),99),"X")),"-",""),"'","")))),ROW(INDIRECT("1:"&LEN(PROPER(LOWER(IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(MID(A2,FIND("W",A2,1),2))=FALSE,MID(A2,FIND(MID(A2,FIND("W",A2,1),2),A2)+3,99),IF(FIND("-",A2,1)<>0,MID(A2,AGGREGATE(14,6,FIND("|",SUBSTITUTE("-"&A2&"-","-","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))+1)))),1),99),"X")),"-",""),"'",""),LEFT(RIGHT(A2,LEN(A2)-FIND("(",A2,1)+1),FIND(")",RIGHT(A2,LEN(A2)-FIND("(",A2,1)+1))),""),SUBSTITUTE(SUBSTITUTE(IF(ISERROR(MID(A2,FIND("W",A2,1),2))=FALSE,MID(A2,FIND(MID(A2,FIND("W",A2,1),2),A2)+3,99),IF(FIND("-",A2,1)<>0,MID(A2,AGGREGATE(14,6,FIND("|",SUBSTITUTE("-"&A2&"-","-","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))+1)))),1),99),"X")),"-",""),"'",""))))))),1),""))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Dossfm0q]

HHH.xlsx
	A	B
1
2	2310672 - P7 W2 Jean Sebestien Pelletier.	J.S.P.
3	3310678 - P7 W2 Natasha Trzebinski.	N.T.
4	3227881 - P1 W3 Jordan Houghton	J.H.
5	3326480 - P8 W3 Evaughn Heath	E.H.
6	3344673 - P9 W4 Katelyn Young	K.Y.
7	3264657 - P4W2 Josee Robitaille	J.R.
8	3228771 - P1 W3 Jean-Sebastien Giroux	J.G.
9	AA3181227
10	45848
11	3012404 - P04 W4 joan McKinley	J.M.
12	3030481 - P05 W1 Valencia Del-hierro	V.D.
13	2954286 - Samantha Veltkamp	S.V.
14	2981640 - P04 W4 Ashton Aubichon-Corrigal	A.A.
15	3121502 - P09 W2 Myla Sky Stanley Osuthorpe	M.S.S.O.
16	3166413 - P10 W4 Sandra NEUFELD	S.N.
17	3187164 - P12 W3 George etzerza	G.E.
18	CREDIT MEMO EQUITABLE LIFE OF CANADA
19	88316376 - P9 W3 Jackie (Jadwiga) Jakinik.	J.J.
20	3521802 - P10 W3 Judit Dell'Anno.	J.D.
21	81745374 - P6 W1 Jasbir Kaur	J.K.
22	88211243 - P6 W2 Frank (huai yang) Sun.	F.S.
23	88218954 - P6 W1 Ashok Gangwar.	A.G.
24	PT-P Chq 28722405 -
25	6911380-Bugg Sondra	B.S.
26	88304744 - P8W2 87`Luciel Lamch	L.L.
27	6911380-Hofer Maria	H.M.
ورقة1

Cell Formulas
Range	Formula
B2:B27	B2	=TEXTJOIN("",1,IF(ISNUMBER(MATCH(CODE(MID(PROPER(LOWER(IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(MID(A2,FIND("W",A2,1),2))=FALSE,MID(A2,FIND(MID(A2,FIND("W",A2,1),2),A2)+3,99),IF(FIND("-",A2,1)<>0,MID(A2,AGGREGATE(14,6,FIND("|",SUBSTITUTE("-"&A2&"-","-","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))+1)))),1),99),"X")),"-",""),"'",""),LEFT(RIGHT(A2,LEN(A2)-FIND("(",A2,1)+1),FIND(")",RIGHT(A2,LEN(A2)-FIND("(",A2,1)+1))),""),SUBSTITUTE(SUBSTITUTE(IF(ISERROR(MID(A2,FIND("W",A2,1),2))=FALSE,MID(A2,FIND(MID(A2,FIND("W",A2,1),2),A2)+3,99),IF(FIND("-",A2,1)<>0,MID(A2,AGGREGATE(14,6,FIND("|",SUBSTITUTE("-"&A2&"-","-","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))+1)))),1),99),"X")),"-",""),"'","")))),ROW(INDIRECT("1:"&LEN(PROPER(LOWER(IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(MID(A2,FIND("W",A2,1),2))=FALSE,MID(A2,FIND(MID(A2,FIND("W",A2,1),2),A2)+3,99),IF(FIND("-",A2,1)<>0,MID(A2,AGGREGATE(14,6,FIND("|",SUBSTITUTE("-"&A2&"-","-","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))+1)))),1),99),"X")),"-",""),"'",""),LEFT(RIGHT(A2,LEN(A2)-FIND("(",A2,1)+1),FIND(")",RIGHT(A2,LEN(A2)-FIND("(",A2,1)+1))),""),SUBSTITUTE(SUBSTITUTE(IF(ISERROR(MID(A2,FIND("W",A2,1),2))=FALSE,MID(A2,FIND(MID(A2,FIND("W",A2,1),2),A2)+3,99),IF(FIND("-",A2,1)<>0,MID(A2,AGGREGATE(14,6,FIND("|",SUBSTITUTE("-"&A2&"-","-","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))+1)))),1),99),"X")),"-",""),"'",""))))))),1)),ROW(INDIRECT("65:90")),0)),MID(PROPER(LOWER(IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(MID(A2,FIND("W",A2,1),2))=FALSE,MID(A2,FIND(MID(A2,FIND("W",A2,1),2),A2)+3,99),IF(FIND("-",A2,1)<>0,MID(A2,AGGREGATE(14,6,FIND("|",SUBSTITUTE("-"&A2&"-","-","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))+1)))),1),99),"X")),"-",""),"'",""),LEFT(RIGHT(A2,LEN(A2)-FIND("(",A2,1)+1),FIND(")",RIGHT(A2,LEN(A2)-FIND("(",A2,1)+1))),""),SUBSTITUTE(SUBSTITUTE(IF(ISERROR(MID(A2,FIND("W",A2,1),2))=FALSE,MID(A2,FIND(MID(A2,FIND("W",A2,1),2),A2)+3,99),IF(FIND("-",A2,1)<>0,MID(A2,AGGREGATE(14,6,FIND("|",SUBSTITUTE("-"&A2&"-","-","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))+1)))),1),99),"X")),"-",""),"'","")))),ROW(INDIRECT("1:"&LEN(PROPER(LOWER(IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISERROR(MID(A2,FIND("W",A2,1),2))=FALSE,MID(A2,FIND(MID(A2,FIND("W",A2,1),2),A2)+3,99),IF(FIND("-",A2,1)<>0,MID(A2,AGGREGATE(14,6,FIND("|",SUBSTITUTE("-"&A2&"-","-","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))+1)))),1),99),"X")),"-",""),"'",""),LEFT(RIGHT(A2,LEN(A2)-FIND("(",A2,1)+1),FIND(")",RIGHT(A2,LEN(A2)-FIND("(",A2,1)+1))),""),SUBSTITUTE(SUBSTITUTE(IF(ISERROR(MID(A2,FIND("W",A2,1),2))=FALSE,MID(A2,FIND(MID(A2,FIND("W",A2,1),2),A2)+3,99),IF(FIND("-",A2,1)<>0,MID(A2,AGGREGATE(14,6,FIND("|",SUBSTITUTE("-"&A2&"-","-","|",ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))+1)))),1),99),"X")),"-",""),"'",""))))))),1)&".",""))
Press CTRL+SHIFT+ENTER to enter array formulas.