Let us see how to format the data in an Excel cell by removing additional information like email addresses and dates/times.
Problem
When working with text data in Excel, you may encounter cells containing email addresses, dates, times, and other unnecessary text you want to remove. For example:
Original text in cell A2:
We want the cell A2 only to show the text values "demo document" and "some comments" and not the email address and the date and time, which are currently part of the cell data.
Solution
We can use an Excel formula to help with removing the additional data. For the example given above we can use the following formula -
=IF(A2<>"", TRIM(SUBSTITUTE(SUBSTITUTE(AU3, MID(A2, FIND("(", A2), FIND(")", A2) - FIND("(", A2) + 1), ""), LEFT(A2, FIND(" ", A2)-1), "")), "")
Steps to apply the formula
1. Insert the formula into an empty column, such as 'B2'
2. Drag the formula down from the corner of the cell to apply it to the other rows
3. Copy and paste the cleaned text as values if you no longer need the original text
Key Excel functions used
Before we proceed with understanding the formula, let's look at the functions used within the formula.
| Function Name | Syntax | Explanation | Example |
| FIND | FIND (find_text, within_text) | Helps to locate any text within another text. | Data in cell A2 - Sample Data Formula = FIND("l", A2) Result - 5 |
| LEFT | LEFT (text, number_of_characters) | Returns a set of characters from the required text based on the number of characters you have given. | Data in cell A2 - Sample Data Formula = LEFT(A2, 4) Result - Samp |
| MID | MID (text, start_num, number_of_characters) | Returns a specific number of characters from a text string, based on the starting number you give and the number of characters you specify) | Data in cell A2 - Sample Data Formula = MID(A2, 8, 4) Result - Data |
| SUBSTITUTE | SUBSTITUTE(text, old_text, new_text) | Replaces a specific text in an existing text string with a new text. | Data in cell A2 - Sample Data Formula = SUBSTITUTE(A2, "Sample", "Test") Result - Test Data |
| TRIM | TRIM(text) | Removes all spaces from your text except for single spaces between words | Formula = TRIM("Sample Data ") Result - Sample Data |
Understanding and applying the formula
Let us take a step-by-step look into the formula above and how it keeps required data and removes what is unnecessary.
Step 1: Check if the cell has data
Before removing the data we need to ensure that the selected cell has data and is not empty.
`IF(A2<>"", ...)` - This check will apply the rest of the formula only if there is some data in the cell
Step 2: Remove the date/time string
Once Excel identifies that the cell has data, it will execute the rest of the formula. The first part will be to remove the date/time values.
=IF(A2<>"", TRIM(SUBSTITUTE(SUBSTITUTE(A2, MID(A2, FIND("(", A2), FIND(")", A2) - FIND("(", A2) + 1), ""), LEFT(A2, FIND(" ", A2)-1), "")), "")
The highlighted portion of the formula uses three distinct Excel functions to find the date/time value before replacing it with no data.
- The FIND function in the formula finds both the beginning and end parentheses of the date/time value and the length of the date/time value.
- The MID function then finds the date/time string that we need to replace by utilizing the values found by the previous function.
- The SUBSTITUTE function then replaces the identified string with no data.
So if we enter just the highlighted portion as a formula in Excel the value reflected will be as seen in the image below -
Step 3: Remove the email addresses
The next step of the process is to remove the email address.
=IF(A2<>"", TRIM(SUBSTITUTE(..., LEFT(A2, FIND(" ", A2)-1), ""))
The highlighted portion of the formula finds the email address in the text by looking for the space after found after the email address. Once the space is found the formula will replace the email address text with no data. Thereby leaving you with the value: - "demo document" - "some comments".
Step 4: Trim extra spaces
The TRIM function in the formula removes all the extra spaces from the modified text
Why this solution works
This approach ensures the dynamic cleaning of the data while keeping the formulas flexible for different rows of data. Combining logical checks, text functions, and substitutions makes it easy to remove unwanted content without affecting other parts of the text.
VBA or Power Query might be more efficient for more complex cases or repeated patterns. Contact support@goldfynch.com for guidance on those approaches!