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

 

Related Posts

How to Insert Dashes in Phone Numbers in Excel
inset dash phone number15

This post will guide you how to insert or add dashes in phone number with a formula in Excel. How do I add dashes to telephone numbers in a selected range in Excel. How to separate numbers with dashes in ...

Extract First or Last N Characters
extract first two character2

This post will guide you how to extract the first or last n characters from a text string in one cell in Excel. How do I get the first or last N characters from a string using an Excel formula. ...

Insert The File Path and Filename into Cell
insert filepath filename in cell5

This post will guide you how to insert the file path and filename into a cell in Excel. Or how to add a file path only in a specified cell with a formula in Excel. Or how to add the ...

Extract Part of Text String
extract part of text string3

This post will guide you how to extract part of text string from a cell in Excel. How do I extract a word from a text string in Excel. How to extract a substring from a text string in a ...

Sum if Cells Begin with Specific Text or Number in Another Cells
sum cells begin specific textor number2

This post will guide you how to sum cells if cells begin with a specific text or number in another column in excel. How do I sum if cells start with specific text or number in another cells in excel. ...

Fill Blank Cells with specific value (0 or null)
fill empty blank cell with value2

This post will teach you how to fill all blank cells with specific value, such as: 0 or any other values in excel. How to locate all blank cells and then fill in all the blanks with any specific value. ...

Delete Rows Based on Cell Value
remove rows based on values3

This post will guide you how to use the Find & Replace feature to delete or remove all rows based on certain cell value in Microsoft Excel. Or how to delete all rows that contain certain value with VBA code ...

How to Copy Formula without Changing Cell References
copy formula without changing reference7

This post will guide you how to copy formula without changing its cell references to make an exact copy in excel. How to copy and paste formulas or links without changing Cell References. How to copy exact formula from one ...

How to Check If the First Letter is capitalized
check if first letter capitalized4

This post explains that how to check if the first letter in a cell is capitalized or not in excel. How to write an excel Macro to check if the first letter in a specified cell is capitalized or not. ...

How to remove all spaces between numbers or words
remove spaces6

This post will describe two quick ways to remove all space characters between numbers or words in excel. How to remove unwanted spaces from cells in a select range of cells in Excel. Assuming that you have a list of ...

Sidebar