How to remove text before or after a specific character in Excel?
Do you want to remove specified text before or after a specific character in Excel? Here I sort out some methods on sovling this problem.
Delete text before or after character by Find and Replace in Excel
Find and Replace function is a function used widely in Excel, and with it, you can remove text before or after a specific character.
Press Ctrl + F to open the Find and Replace dialog, and click Replace tab, and type *, into the Find what text box, and leave blank in the Replace with text box, then click Replace All. See screenshot:
Now, you can see the text before comma are removed.
1. If you want to remove the text after comma, you can type this ,* into the Find what text box.
2. You can change the comma to the character you need.
3. This method will remove all text before the last comma in specified cells.
Delete text before or after character by formula in Excel
In Excel, you also can use formula to delete before or after a specific character.
1. Select a blank cell and type this formula =RIGHT(A1,LEN(A1)-FIND(",",A1)) into it, and press Enter key.See screenshot:
2. Drag the Fill Handle down to the range until all text before comma are removed.
1. Remove the text after comma by using =LEFT(A1,FIND(",",A1)-1).
2. In the formulas, A1 is the cell you want to remove text, and "," is the character you want to remove text before or after it.
3. =RIGHT(A1,LEN(A1)-FIND(",",A1)) will remove all text before the first comma in Cell A1, while =LEFT(A1,FIND(",",A1)-1) will remove all text after the first comma in Cell A1.
Office Tab - Tabbed Browsing, Editing, and Managing of Workbooks in Excel:
Office Tab brings the tabbed interface as seen in web browsers such as Google Chrome, Internet Explorer new versions and Firefox to Microsoft Excel. It will be a time-saving tool and irreplaceble in your work. See below demo:
Click for free trial of Office Tab!
Delete text before character with VBA in Excel
There is a VBA code can help you to remove the text before a specific character.
1. Press Alt + F11 to display the Microsoft Visual Basic for Applications window.
2. In the window, click Insert > Module to show a new module window, then copy the following VBA code into the module window.
VBA: Delete text before a specific character in Excel.
Dim Rng As Range
Dim WorkRng As Range
Dim xChar As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xChar = Application.InputBox("String", xTitleId, "", Type:=2)
For Each Rng In WorkRng
xValue = Rng.Value
Rng.Value = VBA.Right(xValue, VBA.Len(xValue) - VBA.InStrRev(xValue, xChar))
3. Click the Run button, a dialog box pops up for you to select a cell or range you will delete texts before comma, and then click the OK button. See screenshots:
4. Then another dialog pops out, please enter the character you will delete all texts based on it (here we enter a comma), and then click the OK button.
Now all texts before the comma are removed immediately.
Note: The VBA will remove all text before the last character (such as comma) in a specified cell.
Remove text before/after character by splitting cells with Kutools for Excel
This section will introduce the Split Cells utility of Kutools for Excel to help y ou quickly split cells with specific character. Please do as follows:
1. Select the range with texts you want to remove before or after a specific character, and then click Kutools > Text > Split Cells. See screenshot:
2. In the Split Cells dialog box, select the Other option, then type the character into the blank box (here we enter a comma), and then click the OK button. See screenshot:
3. In another popping up dialog box, select a blank cell for locating the texts, and then click the OK button.
Then you can see the selected cells are split by specific character – comma. See screenshot:
Easily remove text before/after character with Kutools for Excel
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
300 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...