Proper & Trim to clean up data

Proper Website: https://support.microsoft.com/en-us/office/proper-function-61db8dc3-a6ca-456e-ab1b-e93ad47da569#:~:text=Capitalizes%20the%20first%20letter%20and,text%20string%20to%20lowercase%20letters.

PROPER(text)

Text is text enclosed in quotation marks, a formula that returns text, or a column reference containing the text you want to partially capitalize.


TRIM(text)

The TRIM function syntax has the following arguments:

Text Required. The text from which you want spaces removed.

As a Project Analyst, I used both the proper and trim functions to clean up payroll excel files of employee names. Not only is this important for making your work look clean, but also trim is essential for making sure your conditional formatting worked. If "John " (with a space) and "John" (no space) are checked for exact match using conditional formatting, it will not highlight either of these. This was important because the employee retention tax credit (ERTC) for 2020 summed up quarter by quarter to max out 10k of eligible gross pay used towards generating the credit per employee. The trim and then conditional formatting ensures that we follow this ERTC rule and prevents us from over generating the refundable credit per employee.

An example: John makes 1k in Q1, 4.5 k in Q2, and 6k in Q3, and 3k in Q4. The conditional formatting is a check I used to make sure that our excel file generated no credit in the Q4 excel tab because by Q3 John has already hit the 10k mark and went over. 

Below is example of using proper & trim; I also used Find & Replace (Ctrl+H) to remove the apostrophe beforehand.

Comments

Post a Comment

Popular posts from this blog

GETPIVOTDATA

Image
GETPIVOTDATA The GETPIVOTDATA function returns visible data from a PivotTable. Honestly, at its simplest, just use "=" and then click on a cell and then the whole syntax will autopopulate. That is how I returned the Billing Gross, Net, and Commission YTD. I think is helpful to use a data validation list. First, use "=" and reference a cell and then just change the item to reference the cell that has data validation. For example, there when you change the media type on the green cell F12, you can get its unbilled gross, net, and commission. Note that the data validation list dropdown may not appear unless you open the workbook on your Excel Desktop and open the workbook in Edit mode.  
Read more

Ranking of UC Schools from Top 110 Ranked National Universities of 2024

Link to GitHub:  https://github.com/sgng-hub/2024unviersityranking Link to Dataset: https://www.kaggle.com/datasets/raymondtoo/the-world-university-rankings-2016-2024 Dataset Official Source: https://www.timeshighereducation.com/world-university-rankings/2024/world-ranking This project is about writing SQL query to find the ranking of top 110 world universities that are part of the University of California System I included a link to Looker Studio visualization of the query     
Read more

Xlookup specific value in the middle of a string

EXCELIFSUN Credits goes to the YouTuber EXCELSIFUN. I was familiar with idea of using MID and XLOOKUP.  However, I liked how he pointed out by adding a zero, it can convert the text after using the MID function to a number so that we can use the xlookup. Also, if you highlight all the cells, then enter the formula in the first cell of the highlighted array, then Ctrl + Enter, you can autofill the formula throughout that whole array.
Read more