This post will guide you how to get text before or after dash character in a given cell in Excel. How do I extract text string before or after dash character in Excel.

1. Video: Get Text before or after Dash Character in Excel

You can watch this short video tutorial to learn how to easily extract text before or after a dash character in Excel using simple functions.

2. Get Text before Dash Character with Formula

Assuming that you have a list of data in range B1:B5, which contain text string. And you want to extract text before or after dash character in a given cell or range. How to do it. You can use a formula based on the LEFT function and the FIND function to extract text before dash character in a given cell in Excel. Like this:

=LEFT(B2,FIND("-",B2)-1)

Select the cell where you want to display the result, then type this formula into a blank cell and press Enter key on your keyboard, and drag the AutoFill handle down to other cells to apply this formula to extract text before dash.

You would notice that all text values before dash character have been extracted into new cells.

Note: Replace “B2” with the cell reference that contains the text you want to extract.

3. Get Text after Dash Character with Formula

If you want to get text values after dash character from a given cell or range in Excel, you can use another formula based on the REPLACE function and the Find function. Like this:

=REPLACE(B2,1,FIND("-",B2),"")

Type this formula into a blank cell and press Enter key on your keyboard, and drag the AutoFill handle down to other cells to apply this formula to extract text after dash.

Let’s see how this formula works:

The Find function will return the position of the first dash character from text string in Cell B1, and pass the returned value to REPLACE function as its second argument.

The REPLACE function will replace all text string from the first character to the first dash character. So that you can get text string after dash character.

Note: If the text in the cell does not contain a dash, the formula will return an error value (e.g. #VALUE!).

To avoid this problem, you can use the IFERROR function in combination with the above formula to display a blank cell instead.

=IFERROR(REPLACE(B2,1,FIND("-",B2),""),"")

4. Get Text Values before and after Dash Character with Text to Column

You can also use Text to Column feature to achieve the same result of extracting text string before and after dash character from the selected range of cells in Excel. Here are the steps:

Step1: select your data that you want to split text string by dash.

Step2: go to DATA tab, click Text to Columns command under Data Tools group. And the Convert Text to columns Wizard dialog will open.

Step3: choose Delimited radio button under Original data type section. Click OK button.

Step4: only check Other check box under Delimiters section, and enter dash (-) character into the Other text box, and click Next button.

Step5: select one Destination cell to place text strings or just click Finish button.

You would notice that the selected cells have been split into two columns by dash character. And one column contain the text string before dash from the original text string. And another column contain the text string after dash character.

5. Related Functions

• Excel Replace function
  The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day) …
• Excel LEFT function
  The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
• Excel FIND function
  The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…