How to Get Text before or after Dash Character in Excel

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.

 

 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)

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.

get text before dash1

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

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.

get text before dash2

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. And 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.

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:

#1 select your data that you want to split text string by dash.

get text before dash3

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

get text before dash4

#3 choose Delimited radio button under Original data type section. Click OK button.

get text before dash5

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

get text before dash6

#5 select one Destination cell to place text strings or just click Finish button.

get text before dash7

get text before dash8

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.

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])…

 

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar