clean up column in PQ

JGordon11

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


after clean:

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

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Split by character transition may solve most of the rows except "Washington, D.C.D.C.".
Power Query:
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
ABCD
1NameClean
2U.S. total*U.S. total*U.S. total*
3PalauPalauPalau
4VermontVt.Vermont
5MassachusettsMass.Massachusetts
6HawaiiHawaiiHawaii
7ConnecticutConn.Connecticut
8MaineMaineMaine
9Rhode IslandR.I.Rhode Island
10New JerseyN.J.New Jersey
11New HampshireN.H.New Hampshire
12PennsylvaniaPa.Pennsylvania
13MarylandMd.Maryland
14New MexicoN.M.New Mexico
15CaliforniaCalif.California
16WashingtonWash.Washington
17Washington, D.C.D.C.Washington, D.C.D.C.
18New YorkN.Y.New York
19VirginiaVa.Virginia
20IllinoisIll.Illinois
21OregonOre.Oregon
22GuamGuamGuam
23DelawareDel.Delaware
24ColoradoColo.Colorado
25MinnesotaMinn.Minnesota
26WisconsinWis.Wisconsin
27FloridaFla.Florida
28Northern Mariana IslandsNorthern Mariana IslandsNorthern Mariana Islands
29IowaIowaIowa
30Puerto RicoP.R.Puerto Rico
31MichiganMich.Michigan
32NebraskaNeb.Nebraska
33South DakotaS.D.South Dakota
34American SamoaAmerican SamoaAmerican Samoa
35KansasKan.Kansas
36KentuckyKy.Kentucky
37ArizonaAriz.Arizona
38AlaskaAlaskaAlaska
39OhioOhioOhio
40NevadaNev.Nevada
41MontanaMont.Montana
42UtahUtahUtah
43TexasTexasTexas
44North CarolinaN.C.North Carolina
45MissouriMo.Missouri
46North DakotaN.D.North Dakota
47IndianaInd.Indiana
48OklahomaOkla.Oklahoma
49South CarolinaS.C.South Carolina
50West VirginiaW.Va.West Virginia
51GeorgiaGa.Georgia
52ArkansasArk.Arkansas
53TennesseeTenn.Tennessee
54IdahoIdahoIdaho
55WyomingWyo.Wyoming
56U.S. Virgin IslandsU.S. Virgin IslandsU.S. Virgin Islands
57LouisianaLa.Louisiana
58AlabamaAla.Alabama
59MississippiMiss.Mississippi
60Marshall IslandsMarshall IslandsMarshall Islands
61MicronesiaMicronesiaMicronesia
62Federal agenciesFederal agenciesFederal agencies
63Dept. of Veterans AffairsDept. of Veterans AffairsDept. of Veterans Affairs
64Dept. of DefenseDept. of DefenseDept. of Defense
65Indian Health ServiceIndian Health ServiceIndian Health Service
66Bureau of PrisonsBureau of PrisonsBureau of Prisons
Sheet1
 
Upvote 0
Solution

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top