Go Back

Remove text by position

While working with Excel, we are able to remove specific parts of a text string by using the REPLACE or SUBSTITUTE  functions.  This step by step tutorial will assist all levels of Excel users in removing text based on their position using two different methods.  

Figure 1. Final result: Remove text by position

Formula 1:  =REPLACE(B3,1,5,"")

Formula 2:  =SUBSTITUTE(B3,"9015-","")

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 SUBSTITUTE function

SUBSTITUTE function replaces a word or text in a text string

=SUBSTITUTE(text, old_text, new_text, [instance_num])

  • text  – the text string containing the word or text we want to replace or change
  • old_text  – any text that we want to replace with new_text
  • new_text  – the text we want to replace old_text with
  • instance_num  – Optional; Specifies which occurrence of old_text we want to replace; If omitted, every occurrence of old_text in text is replaced with new_text

Setting up Our Data

Our table contains two columns: Batch Code (column B) and Variety (column C).  Each batch code contains the data for production date in Julian calendar, and the variety.  We want to remove the production date 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 position

Remove text by position using REPLACE

We want to obtain only the variety from each Batch Code in column B.  Note that the position of the variety is constant in each batch code.

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

Step 1.  Select cell C3

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

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 batch code in column B, while start_num is 1 and num_chars is 5, corresponding to the number of characters we want to remove “9015-”.  Our formula replaces the unwanted characters with an empty string “”, starting from the first character up to the fifth character.  

As a result, the first five characters are removed, leaving the substring “BAP” in cell C3.  

Figure 3. Entering the formula using REPLACE

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

Figure 4. Output: Remove “9015-” by position using REPLACE

Remove text by position using SUBSTITUTE

There is an alternative solution to remove text or characters by position using the SUBSTITUTE function.  Let us follow these steps:

Step 1.  Select cell C3

Step 2.  Enter the formula: =SUBSTITUTE(B3,"9015-","")

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

Our formula directly substitutes the text string “9015-” with an empty string “”. As a result, we are able to obtain the variety in C3, which is “BAP”.  

The results are the same as in the previous method using REPLACE.  

Figure 5. Output: Remove “9015-” by position using SUBSTITUTE

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