Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Data Series in Excel
  • Working With Tables in Excel
  • How to Sort by Color in Excel
  • Excel Bar Chart
  • How to create Gauge Chart in Excel

References

  • How to get address of named range in Excel
  • Basic INDEX MATCH approximate in Excel
  • Count rows with at least n matching values
  • How to use Excel COLUMN Function
  • Find closest match in Excel

Data Validations

  • Excel Data validation specific characters only
  • Excel Data validation require unique number
  • Excel Data validation unique values only
  • Excel Data validation must contain specific text
  • Data validation must not exist in list

Remove text by variable position in a cell in Excel

by

To remove text from a cell when the text is at a variable position, you can use a formula based on the REPLACE function, with help from the FIND function.

Formula

=REPLACE(text,start,FIND(marker,text)+1,"")

Explanation

In the example shown, the formula in C6 is:

=REPLACE(B6,1,FIND(":",B6)+1,"")

How this formula works

The REPLACE function will replace text by position. You can use REPLACE to remove text by providing an empty string (“”) for the “new_text” argument.

Worked Example:   Find function vs Search function in Excel

In this case, we want to remove the labels that appear inside text. The labels vary in length, and include words like “Make”, “Model”, “Fuel economy”, and so on. Each label is followed by a colon and a space. We can use the colon as a “marker” to figure out where the label ends.

Worked Example:   Get first name from full name — Manipulating NAMES in Excel

Working from the inside out, we use the FIND function to get the position of the colon in the text, then add 1 to take into account the space that follows the colon. The result (a number) is plugged into the REPLACE function for the “num_chars” argument, which represents the number of characters to replace.

Worked Example:   How to get top level domain (TLD) in Excel

The REPLACE function then replaces the text from 1 to “colon + 1” with “”. In the example shown, the solution looks like this:

=REPLACE(B6,1,FIND(":",B6)+1,"")
=REPLACE(B6,1,6,"")
=2016

Post navigation

Previous Post:

Put names into proper case — Manipulating NAMES in Excel

Next Post:

Create One-dimensional and Two-dimensional Array

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Return blank if in Excel
  • IF with boolean logic in Excel
  • OR function Examples in Excel
  • How to return blank in place of #DIV/0! error in Excel
  • Not Equal To ‘<>‘ operator in Excel

Date Time

  • Basic timesheet formula with breaks in Excel
  • Convert decimal hours to Excel time
  • How to calculate working days left in month in Excel
  • How to get year from date in Excel
  • Get first Monday before any date in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • Calculate conditional mode with criteria in Excel
  • If cell contains one of many things in Excel
  • Group arbitrary text values in Excel
  • Categorize text with keywords in Excel

General

  • Convert column number to letter in Excel
  • How to create dynamic worksheet reference in Excel
  • How to count total number of cells in a rectangular range in Excel
  • Excel Ribbon Quick Overview For Beginners
  • How to get amount with percentage in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning