Replace text from beginning of a string to variable position

The Excel REPLACE function is used to replace a certain length of text from a given string. We define the starting position, the number of characters we want to remove and the new replacement text. In this article, we will learn how to remove/replace some text from the beginning of a string to a variable position.

Generic Excel Formula to remove text from variable position

=REPLACE(string,1,FIND(text,string),"")

string: It is the string from which you want to remove text.

text: It is the text to which you want to remove text.

Let's see an example to make things clear.

Example: Remove Day Name from Time

In this example, we have punch time associated with day names. We want to remove day names from the data and have time only. The time and day name is separated using a hyphen (-).

Let's use the above generic formula to remove the variable length of characters.

Write this formula in C3 and drag it down.

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

Hit enter and drag it down. Now we have the time text in the range we wanted. We have removed all text before time text.

How does it work?

Let's break down the formula to understand it.

Each formula works inside out. The FIND function returns the location of the given text in a given string. Here it returns the location of the hyphen (-) in every string. For the first string, it returns 8.

Now the formula is =REPLACE(B3,1,8,""). As we know that the REPLACE function replaces n number of characters from a starting position with another text. Here, the text is in B3, starting position is 1, the number of characters we figured out 8 with the help of FIND function, and the replacing character is nothing (""). Hence the REPLACE function removes 8 characters from the beginning of the string, which leaves us with time text only.

Notes:

If the given character is not found, the function will return #VALUE error. It is better to use the IFERROR function to catch errors.
The FIND function is a case sensitive function. If you want to do a case-insensitive search then use the SEARCH function.
In the above formula, we have used a hyphen (-) to find number of characters. You can use any character/s to do so, it can be a colon in excel or any text.

So yeah guys, this is how you can remove a variable length of text from the beginning of the string. I hope it was explanatory. If you have any doubts regarding this topic or any other Excel/2010/2013/2016/2019/365/VBA. We will be happy to help you.

Related Articles:

Replace text from end of a string starting from variable position | The REPLACE function along with the LEN, and FIND function can remove characters from the end of the string.

Remove matching characters in Text | To remove matching characters we won't use REPLACE function. We will use the SUBSTITUTE function. This function automatically removes given text with another text.

Remove leading and trailing spaces from text in Excel | The trailing spaces disturb your data set and it is necessary to remove any trailing or leading space from the text in excel. This formula removes all trailing spaces.

Remove unwanted characters in Excel | To remove unwanted characters we use the SUBSTITUTE function.

Remove Characters From Right | The LEFT function can be used to remove characters from the RIGHT of the text. Actually this function focuses on retaining text on left.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.

COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need filter your data to count specific value. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.