Return first word from a string
This tutorial shows how to return the first word from a string using an Excel formula or VBA
Example: Return first word from a string
|
This formula uses a combination of the Excel IFERROR, LEFT and FIND functions to return the first word in a string. The FIND function is used to return the position of the first occurrence of a space, which is identified by " ", in the string. The position (as a number) that is returned by the FIND function, minus one, is then applied in the LEFT function as a within_text parameter.
The LEFT function then returns the number of characters that is determined by the position of the first space minus one from the left of the string.
If a string contains only one word the formula, without the IFERROR function, will return an error. Therefore, using the IFERROR function and assigning the original text we ensure that if a string contains only one word the formula will return that word. |
Sub Return_first_word_from_string()
'declare variables
Dim ws As Worksheet
Dim val As String
Dim ws As Worksheet
Dim val As String
Set ws = Worksheets("Analysis")
val = ws.Range("B5")
val = ws.Range("B5")
On Error Resume Next
'return the first word from a string
ws.Range("C5") = Split(val, " ")(0)
ws.Range("C5") = Split(val, " ")(0)
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("C5") in the VBA code.
String: Select the string from which you want extract the first word by changing the cell reference ("B5") in the VBA code.
Worksheet Selection: Select the worksheet which captures the string from which you want to extract the first word by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output range by changing the cell reference ("C5") in the VBA code.
String: Select the string from which you want extract the first word by changing the cell reference ("B5") in the VBA code.
Worksheet Selection: Select the worksheet which captures the string from which you want to extract the first word by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Sub Return_first_word_from_string()
'declare variables
Dim ws As Worksheet
Dim firstwrd As String
Dim ws As Worksheet
Dim firstwrd As String
Set ws = Worksheets("Analysis")
On Error Resume Next
'return the first word from a range of strings
For x = 5 To 10
ws.Range("C" & x) = Split(ws.Range("B" & x), " ")(0)
Next x
For x = 5 To 10
ws.Range("C" & x) = Split(ws.Range("B" & x), " ")(0)
Next x
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output column range by changing the column reference ("C") and the row range by changing the row reference which are the values that are assigned x in the VBA code.
Range: Select the range from which you want to return the first word from each cell by changing the column and row references in the VBA code. The column reference is represented by letter "B" and the row reference is represented by the values that are assigned to x which are 5 and 10. Therefore, the range that this code will loop through will be ("B5:B10"). The row numbers are represented by the "x" value which can also be changed.
Worksheet Selection: Select the worksheet which captures the range from which you want to extract the first word by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output column range by changing the column reference ("C") and the row range by changing the row reference which are the values that are assigned x in the VBA code.
Range: Select the range from which you want to return the first word from each cell by changing the column and row references in the VBA code. The column reference is represented by letter "B" and the row reference is represented by the values that are assigned to x which are 5 and 10. Therefore, the range that this code will loop through will be ("B5:B10"). The row numbers are represented by the "x" value which can also be changed.
Worksheet Selection: Select the worksheet which captures the range from which you want to extract the first word by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
EXPLANATION
This tutorial explains how to return the first word from a string using an Excel formula and VBA.
The Excel method uses a combination of the Excel IFERROR, LEFT and FIND functions to return the first word in a string. The FIND function, combined with the (" ") criteria returns the position of the first occurrence of a space (as a number), from which a number one is removed to return the position of the last character of the first word in a cell. This position is then applied inside the LEFT function to return the first word in a string. If there is only one word in the cell, this formula will return an error. Therefore, using the IFERROR function and applying the formula inside it we ensure that if the cell only contains one word, that word is returned instead of an error.
Both of the VBA methods make use of the Split function, with the (" ") criteria to return the first word from a string. The first method shows how the VBA code can be applied if you are only looking to return the first word from a single cell. The second VBA method can be applied if you want to return the first word from multiple cells, where the VBA code will loop through each cell in the specified range and return the first word from each cell.
FORMULA
=IFERROR(LEFT(string,FIND(" ",string)-1),string)
=IFERROR(LEFT(string,FIND(" ",string)-1),string)
ARGUMENTS
string: The string from which you want to retrieve the first word.
string: The string from which you want to retrieve the first word.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Return last word from a string | How to return the last word from a string using Excel or VBA | |
| Return most frequently occurring text | How to return the most frequently occurring text using Excel or VBA | |
| Return last numeric value in a column | How to return the last numeric value in a column using Excel or VBA | |
| Return last numeric value in a row | How to return the last numeric value in a row using Excel or VBA |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| IFERROR Function | The Excel IFERROR function returns a specified value if the first value in the IFERROR formula returns an error, otherwise it will return the standard result | |
| LEFT Function | The Excel LEFT function returns the specified number of characters from a specified string, starting from the left side | |
| FIND Function | The Excel FIND function returns the position of a specific sub-string within a string |