Clear Everything Before the Hyphen with Excel’s REPLACE Formula

Once, I was given a list of phone numbers in their international format.  But for my needs, I did not want to include the country code, and I just wanted the phone number. I was looking for a quick way to remove the country code. I discovered a cool way to do this using the REPLACE formula! Goodbye to manual adjustments!

I explain how you can do this in this detailed article.

Key Takeaways:

• REPLACE lets you remove or swap parts of text in Excel based on position.
• The formula syntax is =REPLACE(old_text, start_num, num_chars, new_text).
• To remove everything before a hyphen, combine REPLACE with FIND.
• An empty string “” removes unwanted text instead of replacing it.
• Troubleshoot with IFERROR for missing hyphens or SUBSTITUTE for last-hyphen cases.

 

Understanding the REPLACE Formula

What Does REPLACE Do?

The REPLACE function in Excel is one of those hidden gems that looks simple at first, but once you understand how it works, it becomes a powerful text-cleaning tool. It’s especially useful when you’re dealing with strings (words, numbers stored as text, codes, IDs, etc.) and you want to swap out part of them based on position.

The REPLACE function in Excel lets you swap part of a text string with something else. It works by specifying:

• which text you want to change,
• the starting position,
• how many characters to replace, and
• what to replace them with.

In simple words: You tell Excel where to start, how much to cut, and what to paste in its place.

Formula Breakdown

=REPLACE(old_text, start_num, num_chars, new_text)

• old_text → The text (or cell) that you want to modify.
• start_num → The position in the text string where the replacement begins.
• num_chars → The number of characters to replace.
• new_text → The text you want to insert.

 

Steps to Clear Everything Before the Hyphen

STEP 1: We need to enter the Replace function next to the cell that we want to clean the data from:

=REPLACE

STEP 2: The Replace arguments:

old_text

Which text do we want to change?

Reference the cell that contains the text string:

=REPLACE(C10,

start_num

Which character do we want to start the replacement from?

We want to remove the country code, so it starts from the first character.

=REPLACE(C10, 1,

 

num_chars

How many characters do we want to replace?

We want to remove all characters up to and including the first hyphen.

We will use the FIND formula.

FIND(“-“, C10) will get the location of the first hyphen.

=REPLACE(C10, 1, FIND(“-“, C10),

new_text

What text will serve as the replacement?

Since we want to remove this, you guessed it! We want the value to be an empty string, which is depicted by the double quotations.

=REPLACE(C10, 1, FIND(“-“, C10), ” “)

STEP 3: Do the same for the rest of the cells by dragging the REPLACE formula all the way down using the left mouse button.

Note that all of the phone numbers are now clean:

 

Troubleshooting Tips

No hyphen

The formula will return an error because FIND can’t locate the character. To avoid this, wrap it in an IFERROR:

=IFERROR(REPLACE(C10,1,FIND(“-“,C10),””),C10)

This way, if there’s no hyphen, it just returns the original text.

Multiple hyphens

The formula only removes up to the first hyphen. If you want to remove everything before the last hyphen, use:

=REPLACE(C10,1,FIND(“~”,SUBSTITUTE(C10,”-“,”~”,LEN(C10)-LEN(SUBSTITUTE(C10,”-“,””)))),””)

This replaces based on the last occurrence of the hyphen.

SUBSTITUTE Formula

Yes, but SUBSTITUTE replaces specific characters or text — it doesn’t work with positions. REPLACE is better for position-based editing.

 

FAQs

1. What is the main use of the REPLACE formula in Excel?

The REPLACE formula is mainly used for cleaning and modifying text strings. It allows you to change specific parts of a string without touching the rest. For example, you can remove prefixes like country codes from phone numbers. It’s also useful for masking sensitive data such as credit card numbers. Overall, it’s a versatile tool for making raw data more usable.

2. How is REPLACE different from SUBSTITUTE?

REPLACE works by position, while SUBSTITUTE works by text match. REPLACE needs you to specify where to start and how many characters to remove. SUBSTITUTE simply finds a word or phrase in the text and replaces it. For example, REPLACE can remove the first 4 characters, but SUBSTITUTE can replace every occurrence of the word “apple.” Knowing which one to use depends on whether you’re working with positions or text values.

3. What happens if there is no hyphen in the text?

If no hyphen exists, the formula FIND(“-“,A1) will return an error. That’s because FIND can’t locate the character and Excel doesn’t know what to do. To handle this, you can wrap the formula in IFERROR. Example: =IFERROR(REPLACE(A1,1,FIND(“-“,A1),””),A1). This way, if no hyphen is found, the formula simply returns the original text instead of breaking.

4. Can REPLACE handle multiple hyphens in a single text string?

By default, REPLACE will only remove up to the first hyphen because that’s what FIND locates. If you want to handle multiple hyphens, you’ll need to use a more advanced formula. For example, to remove everything before the last hyphen, combine SUBSTITUTE and FIND:

=REPLACE(A1,1,FIND(“~”,SUBSTITUTE(A1,”-“,”~”,LEN(A1)-LEN(SUBSTITUTE(A1,”-“,””)))),””)

This targets the last hyphen, not the first. So yes, with the right formula tweaks, REPLACE can handle multiple delimiters.

5. Can I use REPLACE for non-text values like numbers?

Yes, but with a catch. Excel automatically treats numbers as text inside the REPLACE formula, so you can modify them like text. For example, =REPLACE(12345,2,2,”00″) will give 10005. However, if your numbers are formatted for calculations, it’s better to first convert them to text. REPLACE is best used for text-based data, phone numbers, IDs, or codes, rather than raw numeric calculations.