Formula Function

1- Trim

Process Flow to Use the TRIM Function in Excel

1. Open Your Workbook

  • Open the Excel file where you want to clean text data.

2. Identify the Data to Trim

  • Locate the cell or column containing the text that has extra spaces.
  • Example: Column A contains names with irregular spaces.

3. Insert a Helper Column

  • Insert a new column next to the text column (if needed).
  • Example: Add a column titled "Trimmed Text" next to Column A.

4. Enter the TRIM Formula

  • In the first cell of the helper column (e.g., B2), enter the formula: =TRIM(A2)
  • Replace A2 with the reference to the cell you want to clean.

5. Apply the Formula

  • Drag the fill handle (small square at the bottom-right corner of the selected cell) down to apply the formula to other rows.

6. Replace the Original Data (Optional)

  • If you want to replace the original data with the cleaned version:
    1. Copy the cells with the TRIM formula.
    2. Right-click on the original column and choose Paste Special > Values.

7. Verify the Result

  • Check the cleaned text for correctness. Extra spaces should be removed.

8. Save Your Work

  • Save the workbook to ensure the changes are preserved.

Use Cases

  • Clean names: Remove unnecessary spaces in a list of names.
  • Prepare data for analysis: Clean text data to avoid errors in lookups or comparisons.
  • Standardize text: Ensure consistent spacing in your data.

Let me know if you need additional assistance with Excel formulas! 😊



2- Len

Step-by-Step Process Flow to Use the LEN Function in Excel

Step 1: Open Excel

  • Launch Excel and open your desired worksheet.

Step 2: Prepare the Data

  • Enter the text or values you want to calculate the length of in the cells.

Step 3: Select the Cell for the Formula

  • Click on the cell where you want to display the result of the LEN function.

Step 4: Enter the LEN Formula

  • In the selected cell, type the formula: =LEN(text)
  • Replace text with the reference to the cell (e.g., A1) or the actual text you want to analyze.

Step 5: Execute the Formula

  • Press Enter to calculate the length of the text in the referenced cell.

Step 6: Copy the Formula to Adjacent Cells (Optional)

  • Drag the fill handle (small square at the bottom-right corner of the formula cell) down or across to apply the LEN function to other cells.

Step 7: Analyze the Results

  • The result displayed will show the number of characters in the referenced cell, including spaces and special characters
Sample Picture: 


3- Exact Function

Step-by-Step Process Flow to Use the EXACT Function in Excel

Step 1: Open Excel

  • Launch Excel and open your desired worksheet.

Step 2: Prepare the Data

  • Enter the text values you want to compare in two separate cells.

Step 3: Select the Cell for the Formula

  • Click on the cell where you want to display the result of the EXACT function.

Step 4: Enter the EXACT Formula

  • In the selected cell, type the formula: =EXACT(text1, text2)

  • Replace text1 and text2 with:
    • The cell references (e.g., A1, B1).
    • Or the actual text strings you want to compare (e.g., "Hello", "hello").

Step 5: Execute the Formula

  • Press Enter to compare the two texts.
    • If the texts are identical (case-sensitive), the function will return TRUE.
    • If the texts are different, the function will return FALSE.

Step 6: Copy the Formula to Adjacent Cells (Optional)

  • Use the fill handle (small square at the bottom-right corner of the formula cell) to drag and apply the EXACT function to other cells for multiple comparisons.

Step 7: Analyze the Results

  • The result will either be TRUE (if the texts are exactly the same) or FALSE (if they are different).


4- Text Function:

Step-by-Step Process Flow to Use the TEXT Function in Excel

Step 1: Open Excel

  • Launch Excel and open your desired worksheet.

Step 2: Prepare the Data

  • Enter the date in cell A1. For example, 08-Jan-25.

Step 3: Select the Cell for the Formula

  • Click on the cell where you want to display the result of the TEXT function.

Step 4: Enter the TEXT Formula

  • In the selected cell, type the TEXT function as follows:

    1. To display the full day name: =TEXT(A1,"dddd") This will return Sunday.

    2. To display the abbreviated day name: =TEXT(A1,"ddd") This will return Sun.

    3. To display the day as a two-digit number: =TEXT(A1,"dd") This will return 08.

    4. To display the day as a single-digit number (without leading zeros): =TEXT(A1,"d")

      This will return 8.

Step 5: Execute the Formula

  • Press Enter after entering each formula to display the corresponding result.

Step 6: Copy the Formula to Adjacent Cells (Optional)

  • If needed, use the fill handle (small square at the bottom-right corner of the formula cell) to drag the formula to adjacent cells.

Step 7: Analyze the Results

  • The result will vary based on the format you use in the TEXT function:
    • dddd returns the full day name (e.g., Sunday).
    • ddd returns the abbreviated day name (e.g., Sun).
    • dd returns the day as a two-digit number (e.g., 08).
    • d returns the day as a single-digit number without leading zeros (e.g., 8).
Sample Picture: 


5- INT Function

Step-by-Step Process Flow to Use the INT Function in Excel

Step 1: Open Excel

  • Launch Excel and open your desired worksheet.

Step 2: Prepare the Data

  • Enter the number or cell reference that you want to round down to the nearest integer.
  • Example: Enter 12.75 in cell A1.

Step 3: Select the Cell for the Formula

  • Click on the cell where you want to display the result of the INT function.

Step 4: Enter the INT Formula

  • In the selected cell, type the following formula: =INT(number)
  • Replace number with:
    • The reference to the cell containing the number (e.g., A1).
    • Or the actual number itself (e.g., 12.75).

Step 5: Execute the Formula

  • Press Enter to round the number down to the nearest integer.
    • Example: For =INT(A1) where A1 contains 12.75, the result will be 12.

Step 6: Copy the Formula to Adjacent Cells (Optional)

  • If you want to apply the INT function to multiple numbers, use the fill handle (small square at the bottom-right corner of the formula cell) to drag the formula down or across.

Step 7: Analyze the Results

  • The result will display the largest integer that is less than or equal to the given number, essentially rounding down the value.
    • Example:
      • =INT(12.75) → 12
      • =INT(-12.75) → -13
Sample Picture:



Comments

Post a Comment

Popular posts from this blog

How to Use the FILTER Formula

Image
FILTER function Excel for Microsoft 365   Excel for Microsoft 365 for Mac   Excel for the web   Excel 2021   More... The  FILTER  function allows you to filter a range of data based on criteria you define. In the following example we used the formula  =FILTER(A5:D20,C5:C20=H2,"")  to return all records for Apple, as selected in cell H2, and if there are no apples, return an empty string (""). Syntax Examples FILTER used to return multiple criteria In this case, we're using the multiplication operator (*) to return all values in our array range (A5:D20) that have Apples  AND  are in the East region:  =FILTER(A5:D20,(C5:C20=H1)*(A5:A20=H2),"") . FILTER used to return multiple criteria and sort In this case, we're using the previous FILTER function with the SORT function to return all values in our array range (A5:D20) that have Apples  AND  are in the East region, and then sort Units in descending order:  =SORT(FILTER(A...
Read more

Skill Matrix Tool & Excel Format

Image
Skill Matrix Tool About Skill Matrix: A skill matrix tool , also known as a competency matrix or skill assessment matrix, is a valuable resource for organizations and individuals alike. It helps assess and visualize the skill levels and proficiencies of individuals within a team or organization. Here are some benefits of using a skill matrix tool: Identifying Skill Gaps: A skill matrix tool allows you to clearly see where there are gaps in skills within your team or organization. This can help in planning training and development programs to address these gaps and enhance the overall skill set. Training and Development Planning: With a skill matrix in place, you can create targeted training and development plans for individuals. This ensures that employees receive training that is directly relevant to their current skill levels and future career goals. Succession Planning: Skill matrices can assist in identifying potential successors for critical roles within the organization. By un...
Read more

Using Flash Fill in Excel

Image
            Using Flash Fill in Excel Flash Fill automatically fills your data when it senses a pattern. For example, you can use Flash Fill to separate first and last names from a single column, or combine first and last names from two different columns. Note:  Flash Fill is only available in Excel 2013 and later.   Enter the full name in cell C2, and press  ENTER . Start typing the next full name in cell C3. Excel will sense the pattern you provide, and show you a preview of the rest of the column filled in with your combined text. To accept the preview, press  ENTER . If Flash Fill doesn't generate the preview, it might not be turned on. You can go to  Data  >  Flash Fill  to run it manually, or press Ctrl+E. To turn Flash Fill on, go to  Tools  >  Options  >  Advanced  >  Editing Options  > check the  Automatically Flash Fill  box.
Read more