We talked about how to remove all the characters before the first match of the comma character in the previous post. And this post will teach you how to delete text after the first occurrence of a comma character in a text string in Excel.
Remove text after a special character
If you want to remove all the characters after the first match of the comma character in the text string of cell B1, you can create an Excel formula based on the LEFT function and the FIND function.
You can use the FIND function to get the position of the first occurrence of a comma character in the text in B1, then use the LEFT function to extract the first left-character from the first comma. So you can remove the text after the first comma character in the text. You can use the following formula:
See Example here
=FIND(“,”,B1)-1
remove text after first comma in excel
This formula returns the position of the first match of a comma character in a text string in cell B1, then subtract 1 from the position number to get the length of the substitution before the first comma, so it returns 5. and the returned value is its LEFT function as num_chars argument.
remove text after first comma in excel
Remove Text after a specific character with FIND & Select command
Use the Find and Replace command to extract the text after the specified character, just refer to the following steps:
Follow the steps
Step 1
Click “HOME“->”Find&Select”->”Replace…”, then the window of the Find and Replace will appear.
Step 2
click “Replace” Tab, then type ,* into the Find what: text box, and leave blank in the Replace with: text box.
Step 3
click “Replace All”
See the image all characters after the first comma character are removed.
Remove Text after a specific character using FIND&Select command
Another Example
Formula: Remove everything after the second occurrence comma
=LEFT(SUBSTITUTE(B5,",",CHAR(9),2),FIND(CHAR(9),SUBSTITUTE(B5,",",CHAR(9),2),1)-1)
Step 1
In the formulas, B5, “,” and 2 number mean that all contents after the second occurrence comma will be removed from cell A7.
Step 2
You can change the “,” and 2 number to any character and occurrence position number as you need.
Remove everything after the second occurrence comma