Go Back

Remove text by variable position

While working with Excel, we are able to retrieve the specific parts of a text string that we want by using the REPLACE function.  However, when the position of the text we want to obtain varies, we make use of the FIND  function.  This step by step tutorial will assist all levels of Excel users in removing text or characters by variable position.  

Figure 1. Final result: Remove text by variable position

Final formula:  =REPLACE(B3,1,FIND("/",B3),"")

Syntax of the REPLACE function

REPLACE function changes a specific part of a text string with a different text string, based on the number of characters we specify

=REPLACE(old_text, start_num, num_chars, new_text)

The parameters are:

  • old_text  – any text that we want to change or modify
  • start_num – the starting position of the character in old_text that we want to replace with new_text
  • num_chars – the number of characters in old_text that we want to replace with new_text
  • new_text – the text that will replace the characters in old_text

Syntax of the FIND function

FIND locates and returns the starting position of a substring within a text string; FIND is case-sensitive

=FIND(find_text, within_text, [start_num])

The parameters are:

  • find_text – the text we want to find in the second text string    
  • within_text –  the text string containing the text we want to find    
  • start_num – Optional; the starting character where we want to start the search; if omitted, default value is 1, which is the first character of within_text  

Setting up Our Data

Our table contains two columns: Product ID (column B) and Variety (column C).  Each Product ID contains the variety at the last part, right after the slash “/” key.  We want to remove the other text in Product ID and obtain only the variety.  The resulting text string will be recorded in column C under “Variety”.  

Figure 2. Sample data to remove text by variable position

Remove text by variable position

We want to obtain only the variety from each Product ID in column B.  Note that the length of the Product IDs is not uniform, and the position of the variety in each ID is not constant.  

In order to remove characters or text by variable position, we use the REPLACE function combined with the FIND function.  Let us follow these steps:

Step 1.  Select cell C3

Step 2.  Enter the formula: =REPLACE(B3,1,FIND("/",B3),"")

Step 3:  Press ENTER

Step 4:  Copy the formula in cell C3 to cells C4:C6 by clicking the “+” icon at the bottom-right corner of cell C3 and dragging it down

Old_text is the Product ID in column B, while start_num is 1.  Num_chars is determined by the FIND function: FIND("/",B3).  It simply locates the slash key “/” and returns its position in cell B3.  As a result, it returns “8”, because the “/” is the eighth character of B3 “998-ERA/BAP”.  

Our REPLACE function then replaces the unwanted characters in B3 with an empty string “”, starting from the first character up to the eighth character.  

Finally, the first eight characters are removed, leaving the substring “BAP” in cell C3.  

Figure 3. Entering the formula using REPLACE and FIND

Copying the formula to the succeeding cells populates column C with the substrings we want as shown below.  

Figure 4. Output: Remove text by variable position

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Sub ConcatWithStyles() Dim X As Long, Cell As Range, Text As String, Position As Long Range("A3").Value = Space(Evaluate("SUM(LEN(A1:F1))+COLUMNS(A1:F1)-1")) Position = 1 Application.ScreenUpdating = False For Each Cell In Range("A1:F1") With Range("A3") .Characters(Position, Len(Cell.Value)).Text = Cell.Characters(1, Len(Cell.Value)).Text For X = 1 To Len(Cell.Value) With .Characters(Position + X - 1, 1).Font .Name = Cell.Characters(X, 1).Font.Name .Size = Cell.Characters(X, 1).Font.Size .Color = Cell.Characters(X, 1).Font.Color .Strikethrough = Cell.Characters(X, 1).Font.Strikethrough End Sub
An Excelchat Expert solved this problem in 23 mins!
Ex_20 Yes, please will you include the position of the text within the cell as that is impertant too
An Excelchat Expert solved this problem in 21 mins!
Hi Sir/Madam, how to remove duplicate text in 2 rows and print remaining text
An Excelchat Expert solved this problem in 23 mins!
Here are some problems that our users have asked and received explanations on
SubConcatWithStyles() DimXAsLong,CellAsRange,TextAsString,PositionAsLong Range("A3").Value=Space(Evaluate("SUM(LEN(A1:F1))+COLUMNS(A1:F1)-1")) Position=1 Application.ScreenUpdating=False ForEachCellInRange("A1:F1") WithRange("A3") Characters(Position,Len(Cell.Value)).Text=Cell.Characters(1,Len(Cell.Value)).Text ForX=1ToLen(Cell.Value) With.Characters(Position+X-1,1).Font TintAndShade=Cell.Characters(X,1).Font.TintAndShade FontStyle=Cell.Characters(X,1).Font.FontStyle EndWith Next EndWith Position=Position+Len(Cell.Value)+1 Next Application.ScreenUpdating=True EndSub
Solved by A. J. in 27 mins
Sub ConcatWithStyles() Dim X As Long, Cell As Range, Text As String, Position As Long Range("A3").Value = Space(Evaluate("SUM(LEN(A1:F1))+COLUMNS(A1:F1)-1")) Position = 1 Application.ScreenUpdating = False For Each Cell In Range("A1:F1") With Range("A3") Characters(Position, Len(Cell.Value)).Text = Cell.Characters(1, Len(Cell.Value)).Text For X = 1 To Len(Cell.Value) With .Characters(Position + X - 1, 1).Font TintAndShade = Cell.Characters(X, 1).Font.TintAndShade FontStyle = Cell.Characters(X, 1).Font.FontStyle End With Next End With Position = Position + Len(Cell.Value) + 1 Next Application.ScreenUpdating = True End Sub
Solved by G. L. in 20 mins
Trying to use iferror and search however given the position of my statements the wrong info is returned. Can I select the specific position within the text?
Solved by E. H. in 30 mins

Leave a Comment

avatar