If a cell contains text
This tutorial shows how to test if a cell contains text and return a specified value if the test is True or False through the use of Excel formulas or VBA
Example: If a cell contains text
|
This formula uses the Excel ISTEXT function to test if cell B5 contains text. The Excel IF function is then used to test if the Excel ISTEXT function tested TRUE meaning the cell contains text. If the test is TRUE the formula will return a "Contains Text" value, alternatively if the test is FALSE the formula will return a "No Text" value.
|
|
This formula uses the Excel ISNUMBER function to test if the value in cell B5 is numeric. The Excel IF function is then used to test if the Excel ISNUMBER function tested FALSE, meaning that the cell does not entirely comprise numeric value and therefore there is text. If the Excel ISNUMBER function tested FALSE the formula will return a "Contains Text" value, alternatively if it tested TRUE the formula will return a "No Text" value.
|
|
This formula uses the Excel COUNTIF function to test if cell B5 has a text value by returning a value greater than 0. The Excel IF function is then used to test if the Excel COUNTIF function returned a value greater than 0, meaning the cell contains text. If the test is TRUE the formula will return a "Contains Text" value, alternatively if the test is FALSE the formula will return a "No Text" value.
|
Sub If_a_cell_contains_text_with_the_ISTEXT_function()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell contains text, using the ISTEXT function, and then return a specified value
If Application.WorksheetFunction.IsText(ws.Range("B5")) = True Then
If Application.WorksheetFunction.IsText(ws.Range("B5")) = True Then
ws.Range("C5") = "Contains Text"
Else
ws.Range("C5") = "No Text"
End If
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("C5") in the VBA code.
Cell to Test: Select the cell that you want to test by changing the cell reference ("B5") in the VBA code.
Worksheet Selection: Select the worksheet which captures the cell that you want to test if it contains text 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.
True and False Results: In this example if a cell contains text the VBA code will return a value of "Contains Text". If a cell does not contain text the VBA code will return a value of "No Text". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
Output Range: Select the output range by changing the cell reference ("C5") in the VBA code.
Cell to Test: Select the cell that you want to test by changing the cell reference ("B5") in the VBA code.
Worksheet Selection: Select the worksheet which captures the cell that you want to test if it contains text 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.
True and False Results: In this example if a cell contains text the VBA code will return a value of "Contains Text". If a cell does not contain text the VBA code will return a value of "No Text". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
ADDITIONAL NOTES
Note 1: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
Note 1: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
METHOD 2. If a cell contains text with the Excel ISTEXT function using VBA with a For Loop
VBA
Sub If_a_cell_contains_text_with_the_ISTEXT_function()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell contains text, using the ISTEXT function with a For Loop, and then return a specified value
For x = 5 To 8
For x = 5 To 8
On Error Resume Next
If Application.WorksheetFunction.IsText(ws.Cells(x, 2)) = True Then
ws.Cells(x, 3) = "Contains Text"
Else
ws.Cells(x, 3) = "No Text"
End If
Next x
End Sub
ADJUSTABLE PARAMETERS
Output and Test Rows: Select the output rows and the rows that captures the cells that are to be tested by changing the x values (5 to 8). This example assumes that both the output and the associated test cell will be in the same row.
Test Column: Select the column that captures the cells that are to be tested by changing number 2, in ws.Cells(x, 2).
Output Column: Select the output column by changing number 3, in ws.Cells(x, 3).
Worksheet Selection: Select the worksheet which captures the range of cells that you want to test if they contain text 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.
True and False Results: In this example if a range of cells contain text the VBA code will return a value of "Contains Text". If a cell does not contain text the VBA code will return a value of "No Text". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
Output and Test Rows: Select the output rows and the rows that captures the cells that are to be tested by changing the x values (5 to 8). This example assumes that both the output and the associated test cell will be in the same row.
Test Column: Select the column that captures the cells that are to be tested by changing number 2, in ws.Cells(x, 2).
Output Column: Select the output column by changing number 3, in ws.Cells(x, 3).
Worksheet Selection: Select the worksheet which captures the range of cells that you want to test if they contain text 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.
True and False Results: In this example if a range of cells contain text the VBA code will return a value of "Contains Text". If a cell does not contain text the VBA code will return a value of "No Text". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
ADDITIONAL NOTES
Note 1: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
Note 1: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
Sub If_a_cell_contains_text_with_the_ISNUMBER_function()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell contains text, using the ISNUMBER function, and then return a specified value
If Application.WorksheetFunction.IsNumber(ws.Range("B5")) = False Then
If Application.WorksheetFunction.IsNumber(ws.Range("B5")) = False Then
ws.Range("C5") = "Contains Text"
Else
ws.Range("C5") = "No Text"
End If
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("C5") in the VBA code.
Cell to Test: Select the cell that you want to test by changing the cell reference ("B5") in the VBA code.
Worksheet Selection: Select the worksheet which captures the cell that you want to test if it contains text 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.
True and False Results: In this example if a cell contains text the VBA code will return a value of "Contains Text". If a cell does not contain text the VBA code will return a value of "No Text". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
Output Range: Select the output range by changing the cell reference ("C5") in the VBA code.
Cell to Test: Select the cell that you want to test by changing the cell reference ("B5") in the VBA code.
Worksheet Selection: Select the worksheet which captures the cell that you want to test if it contains text 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.
True and False Results: In this example if a cell contains text the VBA code will return a value of "Contains Text". If a cell does not contain text the VBA code will return a value of "No Text". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
ADDITIONAL NOTES
Note 1: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
Note 1: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
METHOD 4. If a cell contains text with the Excel ISNUMBER function using VBA with a For Loop
VBA
Sub If_a_cell_contains_text_with_the_ISNUMBER_function()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell contains text, using the ISNUMBER function with a For Loop, and then return a specified value
For x = 5 To 8
For x = 5 To 8
On Error Resume Next
If Application.WorksheetFunction.IsNumber(ws.Cells(x, 2)) = False Then
ws.Cells(x, 3) = "Contains Text"
Else
ws.Cells(x, 3) = "No Text"
End If
Next x
End Sub
ADJUSTABLE PARAMETERS
Output and Test Rows: Select the output rows and the rows that captures the cells that are to be tested by changing the x values (5 to 8). This example assumes that both the output and the associated test cell will be in the same row.
Test Column: Select the column that captures the cells that are to be tested by changing number 2, in ws.Cells(x, 2).
Output Column: Select the output column by changing number 3, in ws.Cells(x, 3).
Worksheet Selection: Select the worksheet which captures the range of cells that you want to test if they contain text 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.
True and False Results: In this example if a range of cells contain text the VBA code will return a value of "Contains Text". If a cell does not contain text the VBA code will return a value of "No Text". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
Output and Test Rows: Select the output rows and the rows that captures the cells that are to be tested by changing the x values (5 to 8). This example assumes that both the output and the associated test cell will be in the same row.
Test Column: Select the column that captures the cells that are to be tested by changing number 2, in ws.Cells(x, 2).
Output Column: Select the output column by changing number 3, in ws.Cells(x, 3).
Worksheet Selection: Select the worksheet which captures the range of cells that you want to test if they contain text 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.
True and False Results: In this example if a range of cells contain text the VBA code will return a value of "Contains Text". If a cell does not contain text the VBA code will return a value of "No Text". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
ADDITIONAL NOTES
Note 1: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
Note 1: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
Sub If_a_cell_contains_text_with_the_COUNTIF_function()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell contains text, using the COUNTIF function, and then return a specified value
If Application.WorksheetFunction.CountIf(ws.Range("B5"),"*") > 0 Then
If Application.WorksheetFunction.CountIf(ws.Range("B5"),"*") > 0 Then
ws.Range("C5") = "Contains Text"
Else
ws.Range("C5") = "No Text"
End If
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("C5") in the VBA code.
Cell to Test: Select the cell that you want to test by changing the cell reference ("B5") in the VBA code.
Worksheet Selection: Select the worksheet which captures the cell that you want to test if it contains text 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.
True and False Results: In this example if a cell contains text the VBA code will return a value of "Contains Text". If a cell does not contain text the VBA code will return a value of "No Text". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
Output Range: Select the output range by changing the cell reference ("C5") in the VBA code.
Cell to Test: Select the cell that you want to test by changing the cell reference ("B5") in the VBA code.
Worksheet Selection: Select the worksheet which captures the cell that you want to test if it contains text 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.
True and False Results: In this example if a cell contains text the VBA code will return a value of "Contains Text". If a cell does not contain text the VBA code will return a value of "No Text". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
ADDITIONAL NOTES
Note 1: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
Note 1: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
METHOD 6. If a cell contains text with the Excel COUNTIF function using VBA with a For Loop
VBA
Sub If_a_cell_contains_text_with_the_COUNTIF_function()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'calculate if a cell contains text, using the COUNTIF function with a For Loop, and then return a specified value
For x = 5 To 8
For x = 5 To 8
On Error Resume Next
If Application.WorksheetFunction.CountIf(ws.Cells(x, 2)) > 0 Then
ws.Cells(x, 3) = "Contains Text"
Else
ws.Cells(x, 3) = "No Text"
End If
Next x
End Sub
ADJUSTABLE PARAMETERS
Output and Test Rows: Select the output rows and the rows that captures the cells that are to be tested by changing the x values (5 to 8). This example assumes that both the output and the associated test cell will be in the same row.
Test Column: Select the column that captures the cells that are to be tested by changing number 2, in ws.Cells(x, 2).
Output Column: Select the output column by changing number 3, in ws.Cells(x, 3).
Worksheet Selection: Select the worksheet which captures the range of cells that you want to test if they contain text 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.
True and False Results: In this example if a range of cells contain text the VBA code will return a value of "Contains Text". If a cell does not contain text the VBA code will return a value of "No Text". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
Output and Test Rows: Select the output rows and the rows that captures the cells that are to be tested by changing the x values (5 to 8). This example assumes that both the output and the associated test cell will be in the same row.
Test Column: Select the column that captures the cells that are to be tested by changing number 2, in ws.Cells(x, 2).
Output Column: Select the output column by changing number 3, in ws.Cells(x, 3).
Worksheet Selection: Select the worksheet which captures the range of cells that you want to test if they contain text 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.
True and False Results: In this example if a range of cells contain text the VBA code will return a value of "Contains Text". If a cell does not contain text the VBA code will return a value of "No Text". Both of these values can be changed to whatever value you desire by directly changing them in the VBA code.
ADDITIONAL NOTES
Note 1: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
Note 1: If your True or False result is a text value it will need to be captured within quotation marks (""). However, if the result is a numeric value, you can enter it without the use of quotation marks.
EXPLANATION
This tutorial shows how to test if a cell contains text and return a specified value if the test is True or False by using Excel formulas or VBA.
This tutorial provides three Excel methods that can be applied to test if a cell contains text.
The first method uses a combination of an Excel IF and ISTEXT functions. The ISTEXT function test if the selected cell contains text. If it does then the function will return a TRUE value. The IF function is then used to return a specified value if the ISTEXT function returns a value of TRUE, which in this example is "Contains Text". Alternatively, if the ISTEXT function returns a value of FALSE, then the cell does not contain text and the IF function will return the associated value, which in this example is "No Text".
The second method uses a combination of an Excel IF and ISNUMBER functions. The ISNUMBER function test if the selected cell is a numeric value. If the cell is a numeric value, meaning that there are no text values, then the function will return a TRUE value, alternatively if the cell contains a text value, the function will return a FALSE value. The IF function is then used to return a specified value if the ISNUMBER function returns a value of FALSE, which in this example is "Contains Text". Alternatively, if the ISNUMBER function returns a value of TRUE, then the cell does not contain text and the IF function will return the associated value, which in this example is "No Text".
The third method uses a combination of an Excel IF and COUNTIF functions. The COUNTIF function uses the "*" to identify if the cell contains text. If the cell contains text the COUNTIF function will return a value of 1, alternatively it will return a value of 0. The IF function is then used to return a specified value if the COUNTIF function returns a value greater than 0, which in this example is "Contains Text". Alternatively, if the COUNTIF function returns a value of 0, then the cell does not contain text and the IF function will return the associated value, which in this example is "No Text".
This tutorial provides six VBA methods that can be applied to test if a cell contains text and return a specific value. Methods 1, 3 and 5 are applied against a single cell, whilst methods 2, 4 and 6 use a For Loop to loop through all of the relevant cells, as per the example in the image, to test each of the cells in a range and return specific values. The main difference between the examples is how the code determines is the cell contains text.
FORMULA (using ISTEXT function)
=IF(ISTEXT(value)=TRUE, value_if_true, value_if_false)
=IF(ISTEXT(value)=TRUE, value_if_true, value_if_false)
FORMULA (using ISNUMBER function)
=IF(ISNUMBER(value)=FALSE, value_if_true, value_if_false)
=IF(ISNUMBER(value)=FALSE, value_if_true, value_if_false)
FORMULA (using COUNTIF function)
=IF(COUNTIF(value, "*")>0, value_if_true, value_if_false)
=IF(COUNTIF(value, "*")>0, value_if_true, value_if_false)
ARGUMENTS
value: The value or cell that is to be tested.
value_if_true: Value to be returned if the value or cell contains text.
value_if_false: Value to be returned if the value or cell does not contains text.
value: The value or cell that is to be tested.
value_if_true: Value to be returned if the value or cell contains text.
value_if_false: Value to be returned if the value or cell does not contains text.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| If a range contains a specific value | How to test if a range contains a specific value and return a specified value using Excel and VBA methods | |
| Count cells that contain text | How to count cells that contain text using Excel and VBA methods | |
| Sum values if cells contain text | How to sum value if corresponding cells contain text using Excel and VBA methods | |
| Count most frequently occurring text | How to count the most frequently occurring text using Excel and VBA methods | |
| Return most frequently occurring text | How to return the most frequently occurring text using Excel and VBA methods |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| IF Function | The Excel IF function performs a test on specified conditions entered into the formula and returns a specified value if the result is TRUE or another specified value if the result is FALSE | |
| COUNTIF Function | The Excel COUNTIF function returns the number of cells in a range that meet a specified criteria | |
| ISTEXT Function | The Excel ISTEXT function tests a specified value (cell) if it's a text value and returns TRUE if it's a text value or FALSE if it's not a text value | |
| ISNUMBER Function | The Excel ISNUMBER function tests a specified value (cell) if it's a numeric value and returns TRUE if it's a text value or FALSE if it's not a numeric value |