Lets say you have a large Excell file which has items displayed in each cell as follows:
Restoration & Care | ID: 261755.
But you need only the first part and you need to know how you can get rid of everything after the text in each cell. That is to say you want to get rid of the | ID: xxxxxx
Open Your Excel File: Open your Excel file where you have the data.
Create a New Column: Insert a new column next to your data column to store the cleaned text.
Use a Formula to Extract the Required Text:
- Assuming your data starts in cell A1, you can use the following formula in cell B1 to extract the text before the
|:
=LEFT(A1, FIND(“|”, A1) – 1)
- Assuming your data starts in cell A1, you can use the following formula in cell B1 to extract the text before the
- Drag the formula down to apply it to all the rows.
Copy and Paste as Values:
- Copy the new column with the formulas.
- Right-click the copied column and select
Paste Special > Valuesto replace the formulas with the cleaned text.
Remove the Original Column: If needed, you can now delete the original column with the full text.