How to Remove Everything Before Specific Character In Excel

How to Remove Everything Before Specific Character in Excel
Removing text before a certain character within a cell though a regular occurrence, it tends to be critical if you're trying to do it manually. But in a previous tutorial I’ve explained how to remove everything before specific character in excel. Here is the link:    • How to Remove Everything After A Spec...  

This gives us a solid foundation over which we can achieve a lot of things.
Enough bragging. Let’s understand scenario; we have data in column A, and we are writing the formula in column B. Also, in our scenario, we have sentences, but in between the sentences, we have the “@” sign. Our task here is to remove the “@” sign and everything before it. There could be a lot of text in a cell, and the sign “@” could appear anywhere.

In this tutorial, we’ll use simple Excel Text functions like RIGHT, LEN and FIND. The idea is to first locate where the specific character is inside the sentence. The FIND function can do that and will return the character’s position. For example, if the “@” sign is the third character, it will return 3.
As RIGHT function returns characters from the RIGHT and we need to specify how many characters we need to extract from the RIGHT. This generates a new problem. Somehow, we need to calculate the total number of characters from the cell.

This is where the left function comes in. Now we need to use the Len function and then deduct the number we got from the find function and that will give us the character number we need to extract from the RIGHT. Then, we use the RIGHT function to extract everything from the RIGHT, from the position we specify.

If you want to learn more about these functions, check out the following tutorials:
RIGHT Function:    • How to use RIGHT Function in Excel  
FIND Function:    • How to Use FIND Function in Excel  
Len Function:    • How to Use LEN Function in Excel  
Now that we’ve sorted everything out, let’s head to Excel and try out our simplified approach.

Remember, our goal is to successfully remove everything before a specific character from a text inside a cell in Microsoft Excel. Nothing more, nothing less. But once you grasp the idea, you can always tweak it and do something incredible. Have fun!

Step 1:
Open your Excel workbook where we have our data, and either set up your data in column A or tweak the cell reference in the formula we’re about to apply.
Step 2:
Now write the formula in cell B1: =FIND("@",A1) This will give you the character position. If you count each character from the RIGHT, you will find the “@” sign exactly at the position returned by the formula.
Step 3:
Next, we need to deduct value with the value from the LEN function like this: = LEN(A1)-FIND("@",A1))

This will give us the character number to the RIGHT from the specific character, but it will not remove them yet. For that we need the RIGHT function. So, this brings us to our next step.

Step 4:
To remove the specific character and everything before that from our data, we can simply modify our formula and add RIGHT function:
=RIGHT(A1,LEN(A1)-FIND("@",A1))

Step 5:
Now, press enter. This will remove everything Before the specific character, including the character itself.

Optional Steps:
Step 1: If you need to keep only the results and remove the formula from your final column (Column B, in this case), check out this tutorial: 6 Ways to Convert Formula to Values in Excel
Enjoy!

Thanks for watching.
----------------------------------------------------------------------------------------
Support the channel with as low as $5
www.patreon.com/excel10tutorial
----------------------------------------------------------------------------------------
Please subscribe to #excel10tutorial
goo.gl/uL8fqQ

Here goes the most recent video of the channel:
bit.ly/2UngIwS

Playlists:

Excel Tutorial for Beginners: goo.gl/UDrDcA
Intermediate Excel Tutorial: tinyurl.com/59a837py
Advance Excel Tutorial: goo.gl/ExYy7v
Excel Text Case Conversion Techniques: goo.gl/xiP3tv
Combine Workbook & Worksheets: bit.ly/2Tpf7DB
All About Comments in Excel: bit.ly/excelcomments
Excel VBA Programming Course: bit.ly/excelvbacourse
ChatGPT Excel Mastermind: tinyurl.com/46kn7tmd

Social media:
Facebook: facebook.com/excel10tutorial
Twitter: twitter.com/excel10tutorial
Blogger: excel10tutorial.blogspot.com/
Tumblr: excel10tutorial.tumblr.com/
Instagram: www.instagram.com/excel_10_tutorial
Hubpages: hubpages.com/@excel10tutorial
Quora: bit.ly/3bxB8JG
Website: msexceltutorial.com/ Become a member and enjoy exclusive perks while supporting the channel you love!
youtube.com/channel/UCvYWtCPVbIDYLyNqB4lRIaw/join