How to Remove the First Word in Excel: 9 Best Ways

Do you need to remove the first word in Excel for your analysis? This blog post explains you to remove part of the text in Excel. When working with data in Microsoft Excel, it can be difficult to extract information stored in a single column or string of text with the delimiter. This data can be important for sorting, analyzing, and organizing the data. Microsoft Excel offers several functions that can help you easily clean and process the data you need.

Before you get into the techniques to remove the first word in Excel, it is important to understand the concept of a delimiter. A delimiter is a character that separates words or phrases in a text string. Common delimiters in Excel are spaces, semicolons, hyphens, commas, and tabs. When a text string contains a delimiter, each word or phrase is separated by the delimiter.

For example, when dealing with a comma-separated value (CSV) file, the comma is the delimiter. When dealing with a tab-separated value (TSV) file, the tab is the delimiter.

There are a few different techniques to remove first word in Excel. In this blog post, you will learn 9 different methods to remove first word from a text.

There is no direct formula in Excel to remove the first word. You can split the text by delimiter, remove the first word and then join all other words.

You can use a combination of Excel basic functions RIGHT, LEN, FIND and MID to remove the first word or use the advanced Excel function TEXTAFTER if you deal with Office 365.

The Excel features Flash Fill or Find and Replace commands that will help you to remove the first word in Excel. And you have more options to remove the first word in Excel using Power Query or Power Pivot. If you need to remove the first word in your automation, you can use either VBA or Office Scripts code.

Suppose you have received the Employee Details data in a CSV file. It has the Country and Employee Name combined with a comma delimiter and you need to remove the country in each record and retain only the Employee name in an adjacent column for further analysis.

Get your copy of the example workbook used in this blog post to follow along!

Remove First Word in Excel with RIGHT function

In this method, you are going to use four basic excel in-built functions TRIM, RIGHT, LEN and FIND to remove the first word in excel.

TRIM( text )

TRIM function removes all spaces except for single spaces between words.

RIGHT( text, [num_chars] )

RIGHT function will return the last specified number of characters from a text string.

LEN( text )

LEN function will return the number of characters in the given text string.

FIND( findText, withinText, [start_num] ) 

FIND locate the findText string within a withinText string, and return the starting position of the findText string from the first character of the withinText string.

Please follow the below steps to remove the first word Country in the record.

Go to cell B2, type the formula =TRIM( RIGHT( A2, LEN( A2 ) - FIND( ",", A2 ) ) ) and then Press Enter. Drag the fill handle to the range B2:B11 to copy the formula.

In the above formula, the FIND function finds the comma separator in cell A2 and returns the first instance of the comma position and subtracts the position number from the length of the text in cell A2. The RIGHT function returns all the characters from the comma position in cell A2 and finally TRIM function removes all the blank spaces and returns only the Employee Name.

Remove First Word in Excel with MID function

Alternatively, you can use the MID function to remove first word in excel. It allows you to extract a specific number of characters from a text string, starting from the position you specify.

MID( text, start_num, num_chars ) 

It returns the number of characters from the starting position.

Go to cell B2, type the formula =MID( A2, FIND( ",", A2 ) + 1, LEN( A2 ) ) and then Press Enter. Drag the fill handle to the range B2:B11 to copy the formula.

In the above formula, FIND returns the first instance of the comma separator position in cell A2, which is the starting position of the MID function. LEN function returns the number of characters in cell A2.

Remove First Word with Excel 365 TEXTAFTER functions

In this section, you will learn how to use the TEXTAFTER function to remove the first word in excel. Compare with all other approaches it is very easy and simple to use. It will not work in the earlier versions of Excel.

= TEXTAFTER( text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found] ) 

It returns the text that comes after a given character or string. Assign 1 to the optional argument instance_num, since you need to remove the first word country.

Go to cell B2, type the formula =TEXTAFTER( A2, ",", 1 ), and then Press Enter. Drag the fill handle to the range B2:B11 to copy the formula.

TEXTAFTER formula splits the record by comma delimiter and returns all other words except first word country.

Remove First Word in Excel with Flash Fill

Excel Flash Fill is a feature that automatically completes data entry tasks based on patterns that you enter. This feature can help you quickly enter repetitive data in an organized format.

You start entering the Employee Name in column B in cells B2 and B3. Excel will fill in the rest of the data for you. You just press Enter to fill in the values

Remove First Word in Excel with Find and Replace

You can use excel Find and Replace feature to extract the last word.  To use Find and Replace, you just press the keyboard shortcut Ctrl + H to open the Find and Replace dialog box.

Please follow the below steps to remove the first word country.

1. Copy and paste all the records in column B and Select the range B2:B11
2. Press Ctrl + H

3. Type *, in the Find what: input box comma is a delimiter in this example, and just leave blank for Replace with: input box, since you need to remove first word.
4. Press Replace All

Remove First Word in Excel with Power Pivot (DAX)

Power Pivot is an add-in that enables you to quickly process data and build complex data models, such as relationships, hierarchies, and computed columns for further analysis.

1. Select the range of cells A1:A11
2. Choose the command Add to Data Model in the Power Pivot Menu – to open the Power Pivot editor

3. Rename the PowerPivot Table name PPData
4. Rename the Second column Employee Name Type the formula =RIGHT( PPData[Employee Details], LEN( PPData[Employee Details] ) - FIND( ",", PPData[Employee Details] ) ) in the formula bar and Press Enter

5. Select Home Tab
6. Choose Pivot Table Icon
7. Select Flattened PivotTable – to open Create Flattened PivotTable

8. Choose Existing Worksheet
9. Select cell C1 in the Location Input box, where you need to place the data. Press OK

The above steps add an empty pivot table to the PowerPivot worksheet cell C1.

Drag both the fields Employee Details and Employee Name into the Rows in PivotTable Fields.

Remove First Word in Excel with Power Query

Power Query is a powerful ETL tool for Extract, Transform, and Load data. It can be used to connect to a variety of data sources, including databases, files, web pages, and folders. The Power Query graphical user interface allows you to build complex queries. Once the data is properly connected, you can use the Power Query command buttons to transform and format the data for your analysis.

Steps to remove the first word using Power Query

1. Select the range of cells A1:A11
2. Choose From Table/Range option in Data Menu– to open the Power Query window

3. Go to Transform
4. Select Split Column Icon
5. Choose By Delimiter option

6. Select the Left-most delimiter option in the Split at section and Press OK

7. The above steps added a new column, Rename the new column Employee Name.
8. Select the Employee Details.1 column, Click the Right Mouse Button, and then select Remove to remove the Employee Details.1 column.

9. Select Home Tab
10. Choose the option Close & Load To… in the Close & Load Menu

11. Choose the option Table in the Select how you want to view this data in your workbook.
12. Select the Existing worksheet: and choose the cell, where you need to put the data
13. Press OK

You can see the output in your worksheet range D1 to D11

Remove First Word in Excel with VBA Script

VBA allows you to automate tasks within Excel and extend the functionality of Excel and create custom solutions to problems. You can write VBA code to analyse your data, create a dashboard, simulator and automate your reports.

Sub RemoveFirstWord()
' Declare variables
Dim wSheet As Worksheet
Dim tVal As String
Dim rge As Range

' Assign worksheet and selected range to the respective variables
Set wSheet = Worksheets("VBA")
Set rge = ActiveSheet.Range(Selection.Address)

' Loop through the Selected range of cells
For Each rg In rge
    ' Assign the current cell value to the variable tVal
    tVal = rg.Value
    
    ' Put the extracted words to the adjacent cell
    rg.Offset(0, 1) = Right(tVal, Len(tVal) - InStr(tVal, ","))
Next

End Sub

Add the above code to your VBA module.

To Run the RemoveFirstWord macro

1. Select the range of cells A2:A11, and Press Alt + F8, the keyboard shortcut to open the Macro dialog box.
2. Choose the RemoveFirstWord Macro and then Press Run.

Macro assigns the worksheet and the selected range to the respective variables. The code loop through the selected range.  In each iteration, the current cell value assigns to the variable tVal, and compute the formula to remove the first word. Finally, the extracted words are put in an adjacent cell.

Eg: The first record – France,Cade Roach

InStrRev(tVal, “,”) – returns  7, which is the position of the first comma in the example

Len(tVal) – returns 17, The total number of characters in the record

Right – returns Cade Roach last 10 characters from the record.

Remove First Word in Excel with Office Script

Office Scripts is a programming script used for creating and running macros in Excel. It allows you to record and automate repetitive common tasks.

function main(workbook: ExcelScript.Workbook) {

  // getselected range
  let rng = workbook.getSelectedRange();
  let rows = rng.getRowCount();

// loop through the selected range of cells
  for (let i = 0; i < rows; i++) {

    // Assign the cell value to the variable cellValue as string
    let cellValue = rng.getCell(i, 0).getValue() as string

    // Split the contents by delimiter and count the number of words
    let numberOfWords = cellValue.split(",").length as number
    let cWord = ""
    
    // loop through number of words and join the words
    for (let wNo = 1; wNo < numberOfWords; wNo++ ){
      cWord = cWord.concat(cellValue.split(",")[wNo])

      // Add a comma delimiter and a blank space
      if (wNo < numberOfWords-1)  {
        cWord = cWord.concat(", ")
      }
      
    }

    // set the extracted word in the adjacent column
    rng.getCell(i, 1).setValue(cWord)

  }
}

The script assigns the selected range and the number of rows to the respective variables. Code loop through the number of rows. Assigned current value as string to the variable cellValue. The split function split the record by the delimiter comma, length property returns the total number of words and stores it in the variable numberOfWords.

The inner for loop wNo loop through the number of words in the variable cellValue and join all the words except the first word. Finally put the output words into the adjacent cell.

Follow the below steps to run the Remove First Word Office script in your excel

1. Select the range of cells A2:A11
2. Go to Automate menu
3. Select the Remove First Word Script – to open the Code editor on the right side of the excel window Press Run

Conclusions

Most of the time you receive the data in either a comma-separated value (CSV) file or a tab-separated value (TSV) file. In data cleaning and transformation, splitting the data, joining multiple data fields, extracting specific word, and removing first or last word are common tasks.  

Using the Flash Fill feature is a very simple method to remove the first word, but it will not update dynamically. You have to update manually whenever the data changes.

Excel functions work dynamically whenever the data updates. You learned how to use a combination of excel functions to remove first word. Compare with other excel functions, Excel 365 TEXTAFTER function is very simple to use to remove first word.

Additionally, Power Query or Power Pivot are the best options to use when working with larger, external or multiple data sources. Finally, VBA or Office scripts are useful methods If you need to remove the first word in your automaton or dashboard.

Do you know any other method to Remove First Word in Excel? Let me know in the comments section!