clean up column in PQ

[JGordon11]

I can clean this data in VBA by counting the number of capital letters in each row and taking the left part of the string up to the nth capital letter -1, where n = 2,3,4,4,5 for total capital letters = 2,4,5,6,8 respectively. How can I do that in PQ?

before clean:

vaccine status R1.xlsm
	A
1	Name
2	U.S. total*U.S. total*
3	PalauPalau
4	VermontVt.
5	MassachusettsMass.
6	HawaiiHawaii
7	ConnecticutConn.
8	MaineMaine
9	Rhode IslandR.I.
10	New JerseyN.J.
11	New HampshireN.H.
12	PennsylvaniaPa.
13	MarylandMd.
14	New MexicoN.M.
15	CaliforniaCalif.
16	WashingtonWash.
17	Washington, D.C.D.C.
18	New YorkN.Y.
19	VirginiaVa.
20	IllinoisIll.
21	OregonOre.
22	GuamGuam
23	DelawareDel.
24	ColoradoColo.
25	MinnesotaMinn.
26	WisconsinWis.
27	FloridaFla.
28	Northern Mariana IslandsNorthern Mariana Islands
29	IowaIowa
30	Puerto RicoP.R.
31	MichiganMich.
32	NebraskaNeb.
33	South DakotaS.D.
34	American SamoaAmerican Samoa
35	KansasKan.
36	KentuckyKy.
37	ArizonaAriz.
38	AlaskaAlaska
39	OhioOhio
40	NevadaNev.
41	MontanaMont.
42	UtahUtah
43	TexasTexas
44	North CarolinaN.C.
45	MissouriMo.
46	North DakotaN.D.
47	IndianaInd.
48	OklahomaOkla.
49	South CarolinaS.C.
50	West VirginiaW.Va.
51	GeorgiaGa.
52	ArkansasArk.
53	TennesseeTenn.
54	IdahoIdaho
55	WyomingWyo.
56	U.S. Virgin IslandsU.S. Virgin Islands
57	LouisianaLa.
58	AlabamaAla.
59	MississippiMiss.
60	Marshall IslandsMarshall Islands
61	MicronesiaMicronesia
62	Federal agenciesFederal agencies
63	Dept. of Veterans AffairsDept. of Veterans Affairs
64	Dept. of DefenseDept. of Defense
65	Indian Health ServiceIndian Health Service
66	Bureau of PrisonsBureau of Prisons
Table 1

after clean:

vaccine status R1.xlsm
	A
1	Name
2	U.S. total*
3	Palau
4	Vermont
5	Massachusetts
6	Hawaii
7	Connecticut
8	Maine
9	Rhode Island
10	New Jersey
11	New Hampshire
12	Pennsylvania
13	Maryland
14	New Mexico
15	California
16	Washington
17	Washington, D.C.
18	New York
19	Virginia
20	Illinois
21	Oregon
22	Guam
23	Delaware
24	Colorado
25	Minnesota
26	Wisconsin
27	Florida
28	Northern Mariana Islands
29	Iowa
30	Puerto Rico
31	Michigan
32	Nebraska
33	South Dakota
34	American Samoa
35	Kansas
36	Kentucky
37	Arizona
38	Alaska
39	Ohio
40	Nevada
41	Montana
42	Utah
43	Texas
44	North Carolina
45	Missouri
46	North Dakota
47	Indiana
48	Oklahoma
49	South Carolina
50	West Virginia
51	Georgia
52	Arkansas
53	Tennessee
54	Idaho
55	Wyoming
56	U.S. Virgin Islands
57	Louisiana
58	Alabama
59	Mississippi
60	Marshall Islands
61	Micronesia
62	Federal agencies
63	Dept. of Veterans Affairs
64	Dept. of Defense
65	Indian Health Service
66	Bureau of Prisons
Table 1

 

[shaowu459]

Split by character transition may solve most of the rows except "Washington, D.C.D.C.".

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.SplitColumn(Source, "Name", Splitter.SplitTextByCharacterTransition({"a".."z","*"}, {"A".."Z"}), {"Clean", "n"}),
    res= Table.RemoveColumns(Split,{"n"})
in
    res

Book1.xlsx
	A	B	C	D
1	Name			Clean
2	U.S. total*U.S. total*			U.S. total*
3	PalauPalau			Palau
4	VermontVt.			Vermont
5	MassachusettsMass.			Massachusetts
6	HawaiiHawaii			Hawaii
7	ConnecticutConn.			Connecticut
8	MaineMaine			Maine
9	Rhode IslandR.I.			Rhode Island
10	New JerseyN.J.			New Jersey
11	New HampshireN.H.			New Hampshire
12	PennsylvaniaPa.			Pennsylvania
13	MarylandMd.			Maryland
14	New MexicoN.M.			New Mexico
15	CaliforniaCalif.			California
16	WashingtonWash.			Washington
17	Washington, D.C.D.C.			Washington, D.C.D.C.
18	New YorkN.Y.			New York
19	VirginiaVa.			Virginia
20	IllinoisIll.			Illinois
21	OregonOre.			Oregon
22	GuamGuam			Guam
23	DelawareDel.			Delaware
24	ColoradoColo.			Colorado
25	MinnesotaMinn.			Minnesota
26	WisconsinWis.			Wisconsin
27	FloridaFla.			Florida
28	Northern Mariana IslandsNorthern Mariana Islands			Northern Mariana Islands
29	IowaIowa			Iowa
30	Puerto RicoP.R.			Puerto Rico
31	MichiganMich.			Michigan
32	NebraskaNeb.			Nebraska
33	South DakotaS.D.			South Dakota
34	American SamoaAmerican Samoa			American Samoa
35	KansasKan.			Kansas
36	KentuckyKy.			Kentucky
37	ArizonaAriz.			Arizona
38	AlaskaAlaska			Alaska
39	OhioOhio			Ohio
40	NevadaNev.			Nevada
41	MontanaMont.			Montana
42	UtahUtah			Utah
43	TexasTexas			Texas
44	North CarolinaN.C.			North Carolina
45	MissouriMo.			Missouri
46	North DakotaN.D.			North Dakota
47	IndianaInd.			Indiana
48	OklahomaOkla.			Oklahoma
49	South CarolinaS.C.			South Carolina
50	West VirginiaW.Va.			West Virginia
51	GeorgiaGa.			Georgia
52	ArkansasArk.			Arkansas
53	TennesseeTenn.			Tennessee
54	IdahoIdaho			Idaho
55	WyomingWyo.			Wyoming
56	U.S. Virgin IslandsU.S. Virgin Islands			U.S. Virgin Islands
57	LouisianaLa.			Louisiana
58	AlabamaAla.			Alabama
59	MississippiMiss.			Mississippi
60	Marshall IslandsMarshall Islands			Marshall Islands
61	MicronesiaMicronesia			Micronesia
62	Federal agenciesFederal agencies			Federal agencies
63	Dept. of Veterans AffairsDept. of Veterans Affairs			Dept. of Veterans Affairs
64	Dept. of DefenseDept. of Defense			Dept. of Defense
65	Indian Health ServiceIndian Health Service			Indian Health Service
66	Bureau of PrisonsBureau of Prisons			Bureau of Prisons
Sheet1

 

[JGordon11]

Thanks - works great.