JGordon11
Well-known Member
- Joined
- Jan 18, 2021
- Messages
- 843
- Office Version
- 365
- Platform
- 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:
after clean:
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 |