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]

And day after day, new names come in and some others leave the list...

 

[lrobbo314]

This seems like it's working pretty well.

Book2
	A	B	C	D	E	F	G	H	I	J	K
1	Column1	Column2	Column3	Column4	Column5	Column6	Column7	Column8	Column9	Column10	Initials
2	48039	351	8604590990	Z5	43642	251.3	3310672 - P7 W2 Jean Sebestien Pelletier	43642	N43 381 172	Canadian Forces CF H SVCS	J.S.P.
3	48039	351	8604590991	Z5	43642	174.3	3310674 - P7 W2 Jean Sebestien Pelletier	43642	N43 381 172	Canadian Forces CF H SVCS	J.S.P.
4	48039	351	8604590992	Z5	43642	252	3310678 - P7 W2 Natasha Trzebinski	43642	T31741714	Canadian Forces CF H SVCS	N.T.
5	48039	318	8604608537	Z5	43482	275	3227881 - P1 W3 Jordan Houghton	43482	MILITARY	Canadian Forces CF H SVCS	J.H.
6	48039	2831	8604611548	Z5	43678	120.7	3326480 - P8 W3 Evaughn Heath	43678	0	Canadian Forces CF H SVCS	E.H.
7	48039	318	8604640036	Z5	43712	275	3344673 - P9 W4 Katelyn Young	43712	MILITARY	Canadian Forces CF H SVCS	K.Y.
8	48039	318	8604661530	Z5	43556	335.4	3264657 - P4W2 Josee Robitaille	43556	MILITARY	Canadian Forces CF H SVCS	J.R.
9	48039	318	8604686410	Z5	43483	224	3228771 - P1 W3 Jean-Sebastien Giroux	43483	MILITARY	Canadian Forces CF H SVCS	J.G.
10	48039	1525	8604781354	Z5	43686	275	3331046 - P9 W2 Eric Diffey	43686	M62329083	Canadian Forces CF H SVCS	E.D.
11	48039	1525	8604826841	Z5	43686	275	3331047 - P8 W4 Eric Diffey	43686	M62329083	Canadian Forces CF H SVCS	E.D.
12	48938	AA3181227	1402157260	ZZ	43461	-155	AA3181227	43461	AA3181227	First Nations & Inuit Health NHIB
13	48938	1541	1402748094	ZZ	43551	-239.85	45848	43551	45848	First Nations & Inuit Health NHIB
14	48938	1524	1403038618	ZZ	43790	-275	129379	43790	129379	First Nations & Inuit Health NHIB
15	48938	412978	1403077175	ZZ	43615	-228.29	412978	43615	412978	First Nations & Inuit Health NHIB
16	48938	117794	1403228075	ZZ	43759	-209.3	117794	43759	117794	First Nations & Inuit Health NHIB
17	48938	1502	1403312853	ZZ	44082	-230	58702932	44082	58702932	First Nations & Inuit Health NHIB
18	48938	576	1601397002	DA	43327	-135	169217	43327	530161501	First Nations & Inuit Health NHIB
19	48938	1502	1601571163	DA	43573	-3.37	67065	43573	8380045801	First Nations & Inuit Health NHIB
20	48938	1502	1601943361	DA	43384	21.84	412445	43384	4580358302	First Nations & Inuit Health NHIB
21	48938	1502	1602069334	DA	43886	26	4833	43886	4500112901	First Nations & Inuit Health NHIB
22	48938	1526	1602205860	DA	43755	-119.7	117794	43755	6220030903	First Nations & Inuit Health NHIB
23	48938	1578	8601210817	Z5	43085	182.4	2862117 - P13 W2 Matthew Saddleback	43085	4440670701	First Nations & Inuit Health NHIB	M.S.
24	48938	1502	8601243790	Z5	43130	119	2961899 - P02 W1 Hayden Matchee	43130	4580824701	First Nations & Inuit Health NHIB	H.M.
25	48938	1541	8601390598	Z5	43090	182.4	2878264 - P13 W3 Tyler Jacques	43090	510363301	First Nations & Inuit Health NHIB	T.J.
26	48938	1502	8601392256	Z5	43108	240.31	2941274 - P01 W2 Reginald Siddall	43108	3450069601	First Nations & Inuit Health NHIB	R.S.
27	48938	1550	8601688307	Z5	43148	149	2911422 - P02 W3 Logan Gavey	43148	4620832601	First Nations & Inuit Health NHIB	L.G.
28	48938	1502	8601908668	Z5	43069	184.59	2914556 - P12 W4 Kaydence Wahsatnow	43069	4620837101	First Nations & Inuit Health NHIB	K.W.
29	48938	1502	8601915165	Z5	43068	184.59	2914549 - P12 W4 Ferleen Kakeesim	43068	4620169605	First Nations & Inuit Health NHIB	F.K.
30	48938	1579	8601919253	Z5	43126	142	2886711 - P01 W4 Cynthia Sagstven	43126	4430132101	First Nations & Inuit Health NHIB	C.S.
31	48938	1502	8601986103	Z5	43104	228.29	2912954 - P01 W1 Doris Laboucan	43104	4590028501	First Nations & Inuit Health NHIB	D.L.
32	48938	1502	8602069201	Z5	43099	228.29	2935906 - P13 W4 Jolene gray	43099	45901144-01	First Nations & Inuit Health NHIB	J.G.
33	48938	1502	8602082218	Z5	43215	184.59	2975056 - P05 W1 Liza Jerome	43215	76200285-01	First Nations & Inuit Health NHIB	L.J.
34	48938	1502	8602241215	Z5	43209	190.14	3012404 - P04 W4 joan McKinley	43209	110051101	First Nations & Inuit Health NHIB	J.M.
35	48938	1502	8602332261	Z5	43214	131.4	3030481 - P05 W1 Valencia Del-hierro	43214	3760250701	First Nations & Inuit Health NHIB	V.D.
36	48938	1502	8602351369	Z5	43120	180.98	2954286 - Samantha Veltkamp	43120	3710110202	First Nations & Inuit Health NHIB	S.V.
37	48938	1543	8602379944	Z5	43151	228.29	2980489 - P02 W4 Mary Thomas	43151	6830019401	First Nations & Inuit Health NHIB	M.T.
38	48938	1502	8602383392	Z5	43126	228.29	2958912 - P01 W4 Barbara Shindeka	43126	6840047401	First Nations & Inuit Health NHIB	B.S.
Sheet3

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DoubleSpace = Table.TransformColumns(Source,{{"Column7", each try Text.Trim(Text.Replace(Text.Replace(Text.Replace(Text.Replace(_,"  "," "),"(",""),")",""),".","")) otherwise _}}),
    Hyphens = Table.AddColumn(DoubleSpace, "Custom", each if Text.Contains(Text.From([Column7]),"-") then [Column7] else null),
    Split = Table.SplitColumn(Hyphens, "Custom", Splitter.SplitTextByDelimiter(" - ", QuoteStyle.Csv), {"Custom.1", "Custom.2"}),
    toList = Table.AddColumn(Split, "Custom", each try Text.Split([Custom.2]," ") otherwise null),
    tList = Table.AddColumn(toList, "Custom.3", each try List.Transform([Custom], each if Text.Length(Text.Select(_,{"0".."9"})) = 0 then Text.Start(_,1) & "." else null) otherwise null),
    Initials = Table.AddColumn(tList, "Initials", each try Text.Trim(Text.Combine([Custom.3],"")) otherwise null),
    RC = Table.RemoveColumns(Initials,{"Custom.1", "Custom.2", "Custom", "Custom.3"}),
    toUpper = Table.TransformColumns(RC,{{"Initials", Text.Upper, type text}})
in
    toUpper

 

[Andresleo47]

Oh nice, thanks...Can I add it to a Macro? Or should it be added with a Power Query?

 

[lrobbo314]

Oh nice, thanks...Can I add it to a Macro? Or should it be added with a Power Query?

Add it via Power Query.

You select your data in your worksheet. Then, on the 'Data' tab, under 'Get & Transform' click 'From Table'. This will load your data into Power Query. Then, in Power Query, click on 'Advanced Editor' and paste the code from my post. Then, click 'Close and Load'.

 

[Andresleo47]

Ok, nice...I tried it, and the following error pops up: "
Expression.Error: We couldn't find an Excel table named 'Table1'.
Details:
Table1"

Is it normal? Thanks so much!

 

[lrobbo314]

Ok, nice...I tried it, and the following error pops up: "
Expression.Error: We couldn't find an Excel table named 'Table1'.
Details:
Table1"

Is it normal? Thanks so much!

It sounds like you might have already added a table before. Look at the 'Name Manager' under the 'Formula' tab to find out what your table is named, then change this line...

Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]

and replace "Table1" with the name of your table.

 

[Andresleo47]

Thanks lrobbo, however, the Name Manager is empty, as far as I can see

 

[sandy666]

• select your source whole data
• use Ctrl+T
• as I see there is no headers so choose the same : My table has headers - unchecked
• check in Name Manager the name of the table
• refresh query in Power Query Editor (if the code still exist in Advanced Editor)

 

[Andresleo47]

Great, it worked very well taking the headers off... Now the headers look like Column 1, Column 2, etc... How can I copy the resulting Column to the original Excel file? Thanks guys for your help

 

[sandy666]

do the same in original excel file