Tag: Excel Find

How to Split Cells by the First Space in Texts in Excel

Sometimes we may use space to separate texts to different groups in one cell, if we want to split this cell to multiple columns refer to space, we can implement this via ‘Text to Columns’ feature. But is there any way to split one cell to only two cells by the first space in the texts? In this free tutorial, we will share you some useful functions in excel to split cells per your demand.

Precondition:

In product list we use space to separate product properties.

How to Split Cells by the First Space in Texts in Excel1

Now we want split it to two parts in two different columns by the first space. See expectation below:

How to Split Cells by the First Space in Texts in Excel2

1. Split Cells to Two Columns by the First Space in Texts by Formula

Step1: In B2 enter the formula:

=LEFT(A2,FIND(" ",A2)-1)
How to Split Cells by the First Space in Texts in Excel3

Step2: Click Enter to get result. Verify that text before the first space is extracted and saved in B2 properly.

How to Split Cells by the First Space in Texts in Excel4

Step3: Drag the fill handle down till the end of the list. Verify that texts from the first space are displayed properly.

How to Split Cells by the First Space in Texts in Excel5

Step4: In C2 enter the below formula to extract the left texts after the first space.

=RIGHT(A2,LEN(A2)-FIND(" ",A2))
How to Split Cells by the First Space in Texts in Excel6

Step5: Click Enter to get result. Verify that texts are displayed properly.

How to Split Cells by the First Space in Texts in Excel7

Step6: Drag the fill handle down till the end of the list. Now texts are split to two columns by the first space properly.

How to Split Cells by the First Space in Texts in Excel8

2. Split Cells to Two Columns by the First Space in Texts with VBA Code

You can use a VBA Code that prompts the user to select a range of cells and a destination cell, and then splits the cells in the selected range to two columns based on the first space in the text.

Step1: Press Alt + F11 to open the Visual Basic Editor.

Step2: In the Visual Basic Editor, go to “Insert” on the top menu and select “Module“.

Step3: Paste the below VBA code into the new module.

Split Cells to Two Columns by the First Space in Texts vba 1.png
Sub SplitCellsByFirstSpace_ExcelHow()
    ' Prompt the user to select a range of cells
    Dim selectedRange As Range
    Set selectedRange = Application.InputBox("Select a range of cells:", Type:=8)
    
    ' Prompt the user to select a destination cell
    Dim destinationCell As Range
    Set destinationCell = Application.InputBox("Select a destination cell:", Type:=8)
    
    ' Insert the formula to split the text at the first space
    For Each cell In selectedRange
        cell.Offset(0, 2).Formula = "=RIGHT(" & cell.Address(False, False) & ",LEN(" & cell.Address(False, False) & ")-FIND("" "", " & cell.Address(False, False) & ",1))"
        cell.Offset(0, 1).Formula = "=LEFT(" & cell.Address(False, False) & ",FIND("" "", " & cell.Address(False, False) & ",1)-1)"
    Next cell
    
    ' Copy the formula to the destination cell and adjacent cell
    selectedRange.Offset(0, 1).Resize(selectedRange.Rows.Count, 2).Copy destinationCell
    
    ' Convert the formula to values in the destination cells
    destinationCell.Resize(selectedRange.Rows.Count, 2).Value = destinationCell.Resize(selectedRange.Rows.Count, 2).Value
End Sub

Step4: Press F5 to run the macro or click the “Run” button on the toolbar.

Step5: A dialog box will appear asking you to select a range of cells. Click and drag your mouse to select the range of cells you want to split.

Split Cells to Two Columns by the First Space in Texts vba 2.png

Step6: Another dialog box will appear asking you to select a destination cell. Click on the cell where you want to place the split data.

Split Cells to Two Columns by the First Space in Texts vba 3.png

Step7: The macro will split the data in the selected range into two columns based on the first space in the text, and place the split data into the destination cell and adjacent cells.

Split Cells to Two Columns by the First Space in Texts vba 4.png

3. Video: Split Cells to Two Columns by the First Space in Texts

This video will demonstrate how to split cells to two columns by the first space in texts using Excel formulas and VBA code.

4. Related Functions

  • Excel LEFT function
    The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
  • Excel FIND function
    The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
  • Excel RIGHT function
    The Excel RIGHT function returns a substring (a specified number of the characters) from a text string, starting from the rightmost character.The syntax of the RIGHT function is as below:= RIGHT (text,[num_chars])…
  • Excel LEN function
    The Excel LEN function returns the length of a text string (the number of characters in a text string).The syntax of the LEN function is as below:= LEN(text)…

How to Count Spaces before the Text String

This post will guide you how to count the number of spaces before the text string in a cell in Excel. How to count the leading spaces before the text string in one cell with formula in excel. How do I create an excel formula that counts the number of spaces before the text string.

1. Count number of spaces before a string using a Formula

Assuming that you have lots of cells contain text string with different levels of indentation on them. You want to count the number of spaces before each test string in cells, and then you want to know how to count the number of spaces before the text string in each cell.

You can create an excel formula based on the FIND function, the LEFT function, and the TRIM function.

For example, let’s count the number of spaces before text string in Cell B1, you can write down the following excel formula:

=FIND(LEFT(TRIM(B1),1),B1)-1
count spaces before text1

Let’s see how this formula works:

The TRIM function can be used to remove all spaces at the start or end of the text string, then you can get a text string without any spaces. The returned result will pass into the LEFT function to get the leftmost characters of the result without spaces.

Then use the FIND function to search for that characters in the original string, and it returns the position of the first character of the searching characters in the original string. The result is subtracted 1 to get the number of spaces before the text string in Cell B1.

When you use this formula, you do not need to care how many other spaces there are or where they are.

If there are no spaces at the end of the text string in each cell, then you also can use the following formula:

=LEN(B1)- LEN(TRIM(B1))
count spaces before text2

2. Count Spaces before the Text String using User Defined Function with VBA Code

You can create a user-defined function in Excel using VBA (Visual Basic for Applications) to count the number of spaces before a text string. Just do the following steps:

Step1: Press Alt + F11 to open the Visual Basic Editor.

Step2: Click Insert > Module to insert a new module.

Step3: Type the following code into the module:

How to Count Spaces before the Text String vba 1.png
Function CountSpaces_Excelhow(str As String) As Integer
    Dim i As Integer
    For i = 1 To Len(str)
        If Mid(str, i, 1) <> " " Then
            Exit For
        End If
    Next i
    CountSpaces_Excelhow = i - 1
End Function

Step4: Enter the formula into a cell, then press Enter key to apply it.

=CountSpaces_Excelhow(B1)
How to Count Spaces before the Text String vba 2.png

3. Video: Count Spaces before the Text String

This video will show you how to count spaces before the text string in Excel using a formula or VBA code.

4. Related Formulas

  • Get first word from text string
    If you want to extract the first word from a text string in a cell, you can use a combination of the IF function, the ISERR function, the LEFT function and the FIND function to create a complex excel formula..…
  • Get last word from text string
    If you want to get the last word from a text string, you can create an excel formula based on the RIGHT function, the LEN function, the FIND function and the SUBSTITUTE function..…
  • Extract nth word from text string
    If you want to extract the nth word from a text string in a single cell, you can create an excel formula based on the TRIM function, the MID function, the SUBSTITUTE function, the REPT function and the LEN function..…
  • count specific words in a cell or a range
    If you want to count the number of a specific word in a single cell, you need to use the SUBSTITUTE function to remove all that certain word in text string, then using LEN function to calculate the length of the substring that without that specific word.…

5. Related Functions

  • Excel LEFT function
    The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
  • Excel FIND function
    The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
  • Excel LEN function
    The Excel LEN function returns the length of a text string (the number of characters in a text string).The syntax of the LEN function is as below:= LEN(text)…
  • Excel TRIM function
    The Excel TRIM function removes all spaces from text string except for single spaces between words.  You can use the TRIM function to remove extra spaces between words in a string.The syntax of the TRIM function is as below:= TRIM (text)….

How to Remove the First/Last Word from Text string in Cell

This post will guide you how to remove the first and the last word from a text string in cells using a formula or User defined function with VBA code in Excel 2013/2016/2019/365. How do I use a formula to remove first and last word of a text string in Excel.

1. Remove the First Word from Text String using Formula

If you want to remove the first word from a text string in cells in Excel, you can use a formula based on the RIGHT function, the LEN function and the FIND function. Like this:

=RIGHT(B1,LEN(B1)-FIND(" ",B1))

Type this formula into a blank cell and press Enter key on your keyboard and then drag the AutoFill Handle down to other cells to apply this formula.

remove first last word in cell1

2. Remove the Last Word from Text String using Formula

If you want to remove the last word from a text string, you can use a formula based on the LEFT function, the TRIM function, the FIND function, and the SUBSTITUTE function. Like this:

=LEFT(TRIM(B1),FIND("~",SUBSTITUTE(B1," ","~",LEN(TRIM(B1))-LEN(SUBSTITUTE(TRIM(B1)," ",""))))-1)

Type this formula into a blank cell and press Enter key on your keyboard and then drag the AutoFill Handle down to other cells to apply this formula.

remove first last word in cell2

3. Remove the First Word from Text String using User Defined Function with VBA Code

You can create a User Defined Function in VBA to remove the first word from a text string in Excel by following these steps:

Step1: Open the Visual Basic Editor (VBE) by pressing Alt+F11.

Step2: In the VBE, click on Insert -> Module to create a new module.

Step3: In the module, enter the following code:

How to Remove the FirstLast Word from Text string in Cell vba 1.png
Function RemoveFirstWord_ExcelHow(ByVal inputString As String) As String
    Dim firstSpaceIndex As Integer
    firstSpaceIndex = InStr(1, inputString, " ")
    If firstSpaceIndex > 0 Then
        RemoveFirstWord_ExcelHow = Mid(inputString, firstSpaceIndex + 1, Len(inputString))
    Else
        RemoveFirstWord_ExcelHow = ""
    End If
 
End Function

Step4: Save the module and return to the Excel workbook.

Step5: In a blank cell, enter the following formula:

=RemoveFirstWord(B1)

Where B1 is the cell that contains the text string you want to remove the first word from.

Step6: Press Enter to display the result.

How to Remove the FirstLast Word from Text string in Cell vba 2.png

4. Remove the Last Word from Text String using User Defined Function with VBA Code

If you also want to remove the last word from a text string in excel using a User Defined Function with VBA code, and you can refer to the above steps, and just using the following code:

How to Remove the FirstLast Word from Text string in Cell vba 2.png
Function RemoveLastWord_ExcelHow(ByVal inputString As String) As String
    Dim lastSpaceIndex As Integer
    lastSpaceIndex = InStrRev(inputString, " ")
    If lastSpaceIndex > 0 Then
        RemoveLastWord_ExcelHow = Left(inputString, lastSpaceIndex - 1)
    Else
        RemoveLastWord_ExcelHow = ""
    End If
End Function

In Cell E1, type the following formula, press Enter key to apply it:

=RemoveLastWord_ExcelHow(B1)
How to Remove the FirstLast Word from Text string in Cell vba 4.png

5. Video: Remove the First/Last Word from Text string in Cell

This video will demonstrate how to remove the first/last word from a text string using a formula and VBA code.

6. Related Functions

  • Excel LEFT function
    The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
  • Excel FIND function
    The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
  • Excel RIGHT function
    The Excel RIGHT function returns a substring (a specified number of the characters) from a text string, starting from the rightmost character.The syntax of the RIGHT function is as below:= RIGHT (text,[num_chars])…
  • Excel Substitute function
    The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num])….
  • Excel LEN function
    The Excel LEN function returns the length of a text string (the number of characters in a text string).The syntax of the LEN function is as below:= LEN(text)…
  • Excel TRIM function
    The Excel TRIM function removes all spaces from text string except for single spaces between words.  You can use the TRIM function to remove extra spaces between words in a string.The syntax of the TRIM function is as below:= TRIM (text)….

Extract Email Address from Text

This post will guide you how to extract email address from a text string in Excel. How do I use a formula to extract email address in Excel. How to extract email address from text string with VBA Macro in Excel.

Assuming that you have a list of data in range B1:B5 which contain text string and you want to extract all email addresses from those text string. How to achieve it. You can use a formula or VBA Macro to achieve the result. Let’s see the below introduction.

1. Extract Email Address from Text with a Formula

To extract email address from text string in cells, you can use a formula based on the TRIM function, the RIGHT function, the SUBSTITUTE function, the LEFT function, the FIND function, the REPT function and the LEN function. Just like this:

=TRIM(RIGHT(SUBSTITUTE(LEFT(B1,FIND(" ",B1&" ",FIND("@",B1))-1)," ",REPT(" ",LEN(B1))),LEN(B1)))

Select the adjacent Cell C1, and type this formula, and press Enter key in your keyboard, and then drag the AutoFill Handle over other cells to apply this formula.

exctract email address from text1

2. Extract Email Address from Text with User Defined Function

You can also write a User Defined Function with VBA Code to extract email address quickly, just do the following steps:

Step1: open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

Step2: then the “Visual Basic Editor” window will appear.

Step3: click “Insert” ->”Module” to create a new module.

convert column number to letter3

Step4: paste the below VBA code into the code window. Then clicking “Save” button.

Function ExtractEmailFromText(s As String) As String
    Dim AtTheRateSignSymbol As Long
    Dim i As Long
    Dim TempStr As String
    Const CharList As String = "[A-Za-z0-9._-]"
    
    AtTheRateSignSymbol = InStr(s, "@")
    If AtTheRateSignSymbol = 0 Then
        ExtractEmailFromText = ""
    Else
        TempStr = ""
        For i = AtTheRateSignSymbol - 1 To 1 Step -1
            If Mid(s, i, 1) Like CharList Then
                TempStr = Mid(s, i, 1) & TempStr
            Else
                Exit For
            End If
        Next i
        
        If TempStr = "" Then Exit Function
        
        TempStr = TempStr & "@"
        
        For i = AtTheRateSignSymbol + 1 To Len(s)
            If Mid(s, i, 1) Like CharList Then
                TempStr = TempStr & Mid(s, i, 1)
            Else
                Exit For
            End If
        Next i
    End If
    
    If Right(TempStr, 1) = "." Then TempStr = Left(TempStr, Len(TempStr) - 1)
    
    ExtractEmailFromText = TempStr
End Function

Step5: Type the following formula into blank cells and then press Enter key.

=ExtractEmailFromText(B1)

Type this formula into a blank cell and then press Enter key in your keyboard.

Step6: lets see the result:

exctract email address from text3

3. Video: Extract Email Address from Text in Excel

This video will demonstrate a step-by-step instruction on how to use the formula and VBA code to extract email addresses from a block of text in Excel, making it easy to manage and organize your contact information.

How to extract text after the second or nth specific character (space or comma)

In the previous post, we talked that how to extract text after the first occurrence of the comma character in excel. And this post explains that how to get a substring after the second or nth occurrence of the comma or space character in excel.

1. Extract Text after The Second or Nth Specific Character

If you want to extract text after the second or nth comma character in a text string in Cell B1, you need firstly to get the position of the second or nth occurrence of the comma character in text.

So you can use the SUBSTITUTE function to replace the second comma character with the hash character, then using the FIND function to look for the first hash character in text returned by substitute function, the returned value is the position of the second occurrence of the comma character in text.

Last, you can use the MID function to extract a substring that you want to extract.

So you can create a formula based on the MID function, the FIND function and the SUBSTITUTE function as follows:

=TRIM(MID(B1,FIND("#",SUBSTITUTE(B1,",","#",2))+1,255))

Let’s see how this formula works:

=SUBSTITUTE(B1,”,”,”#”,2)

extract text before second comma1

This function replaces the second comma character with hash character in cell B1, and the returned result goes into the FIND function as its within_text argument.

=FIND(“#”,SUBSTITUTE(B1,”,”,”#”,2))+1

extract text before second comma2

The FIND function will return the position of the first occurrence of the hash character in within_text string returned by the SUBSTITUTE function. Then add 1 to get the starting position after the second occurrence of the comma character in text. And it returns 11. It is fed into the MID function as its start_num argument.

=MID(B1,FIND(“#”,SUBSTITUTE(B1,”,”,”#”,2))+1,255)

extract text after second comma1

The MID function will extracts 255 characters from a text string in Cell B1, starting at the 11character.

=TRIM()

The TRIM function removes all extra space characters and just leave only one between words.

If you want to extract a substring after the third occurrence of the comma character, you can use the following formula:

=TRIM(MID(B1,FIND("#",SUBSTITUTE(B1,",","#",3))+1,255))
extract text after second comma2

2. Extract Text after The Second or Nth Specific Character using User Defined Function with VBA

You can use the VBA Split function to split a string into an array of substrings based on a specified delimiter, and then extract the desired substring based on its index in the array.

You can use a user-defined function that extracts the substring after the third occurrence of the comma character, just do the following steps:

Step1: Press the Alt + F11 keys on your keyboard to open the VBA Editor.

Step2: In the VBA Editor, insert a new module by clicking on “Insert” in the top menu and then selecting “Module“.

Step3: Copy the VBA code that you want to run and paste it into the new module.

How to extract text after the second or nth specific character vba1.png

Step4: Save the module by clicking on “File” in the top menu and then selecting “Save“.

Step5: in a blank cell, type the following formula:

=ExtractSubstring_ExcelHow(B1)

Step6: Press Enter to apply the formula and see the result in the given cell.

How to extract text after the second or nth specific character vba2.png

The function should now be applied to the selected cell and the output should be the desired substring after the third occurrence of the comma character.

3. Video: Extract Text after The Second or Nth Specific Character

This video will show you how to extract a substring after the third occurrence of the comma character in Excel using both a formula and VBA code.

4. Related Formulas

  • Extract Text between Parentheses
    If you want to extract text between parentheses in a cell, then you can use the search function within the MID function to create a new excel formula…
  • Extract Text between Brackets
    If you want to extract text between brackets in a cell, you need to create a formula based on the SEARCH function and the MID function….
  • Extract Text between Commas
    To extract text between commas in Cell B1, you can use the following formula based on the SUBSTITUTE function, the MID function and the REPT function…..
  • Extract word that starting with a specific character
    Assuming that you have a text string that contains email address in Cell B1, and if you want to extract word that begins with a specific character “@” sign, you can use a combination with the TRIM function, the LEFT function, the SUBSTITUTE function ….
  • Extract text before first comma or space
    If you want to extract text before the first comma or space character in cell B1, you can use a combination of the LEFT function and FIND function….
  • Extract text after first comma or space
    If you want to get substring after the first comma character from a text string in Cell B1, then you can create a formula based on the MID function and FIND function or SEARCH function ….
  • Extract text before the second or nth specific character
    you can create a formula based on the LEFT function, the FIND function and the SUBSTITUTE function to Extract text before the second or nth specific character…

5. Related Functions

  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)….
  • Excel FIND function
    The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
  • Excel SEARCH function
    The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH  (find_text, within_text,[start_num])…
  • Excel Substitute function
    The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num])….
  • Excel TRIM function
    The Excel TRIM function removes all spaces from text string except for single spaces between words.  You can use the TRIM function to remove extra spaces between words in a string.The syntax of the TRIM function is as below:= TRIM (text)….

How to Get the Position of the nth Occurrence of a Character in a Cell

In the previous post ,we talked that how to get the position of the last occurrence of a character in a cell, and sometimes, you may be want to know the position of the 2th, 3th or nth occurrence of a character in a text string in excel. and this post will guide you how to find the 2th, 3th, or nth occurrence of a character in a text string using excel formula and use defined function.

1. Get the Position of the Nth Occurrence of a Character using Excel Formula

If you want to get the position of the nth occurrence of a character using a excel formula, you can use the FIND function in combination with the SUBSTITUTE function. For example, to get the position of the 2th occurrence of the character “e” in Cell B1, you can create the following formula:

=FIND("#",SUBSTITUTE(B1,"e","#",2))

The SUBSTITUTE function will replace the 2th occurrence of the character “e” with the hash character. So it returns another text string as “exc#l”.

Get the position of the nth using excel formula1

The FIND function look up the hash character in the text string returned by the SUBSTITUTE function, and it returns the position of the first hash character in string “exc#l”. And it should be the position of the 2th occurrence of the character “e” in Cell B1.

Get the position of the nth using excel formula2

2. Get the Position of the Nth Occurrence of a Character using User Defined Function

You can also create a new user defined function to get the position of the nth occurrence of a specific character or string in Excel VBA:

Step1: click on “Visual Basic” command under DEVELOPER Tab.

Get the position of the nth using excel vba1

Step1: then the “Visual Basic Editor” window will appear.

Step2: click “Insert” ->”Module” to create a new module named as: getNthPosition

Get the position of the nth using excel vba1

Step3: paste the below VBA code into the code window. Then clicking “Save” button.

Get the position of the nth using excel vba1
Function getNthPosition(sFindValue As String, sTextString As String, N As Integer) As Integer
    Dim i As Integer
    Application.Volatile
    getNthPosition = 0
    For i = 1 To N
            getNthPosition = InStr(getNthPosition + 1, sTextString, sFindValue)
        If getNthPosition = 0 Then Exit For
    Next
End Function

Step4: back to the current workbook, then enter the below formula in Cell C1:

=getNthPosition("e",B1,2)
Get the position of the nth using excel vba4

3. Video: Get the Position of the Nth Occurrence of a Character

In this video, you will learn a formula and VBA code that can be used to get the position of the Nth occurrence of a character in a string.

4. Related Formulas

  • Get the position of Last Occurrence of a value in a column
    If you want to find the position number of the last occurrence of a specific value in a column (a single range), you can use an array formula with a combination of the MAX function, IF function, ROW function and INDEX Function.
  •  Get the position of Last Occurrence of a character or string in a cell
    If you want to get the position of the last occurrence of a character in a cell, then you can use a combination of the LOOKUP function, the MID function, the ROW function, the INDIRECT function and the LEN function to create an excel formula.…
  • Combine Text from Two or More Cells into One Cell
    If you want to combine text from multiple cells into one cell and you can use the Ampersand (&) symbol.If you are using the excel 2016, then you can use a new function TEXTJOIN function to combine text from multiple cells…
  • Split Text String to an Array
    If you want to convert a text string into an array that split each character in text as an element, you can use an excel formula to achieve this result. the below will guide you how to use a combination of the MID function, the ROW function, the INDIRECT function and the LEN function to split a string…
  • Find the Relative Position in a Range or Table
    If you want to know the relative row position for all rows in an Excel Range (B3:D6), you can use a excel Array formula as follows:=ROW(B3:D6)- ROW(B3) + 1. You can also use another excel array formula to get the same result as follows:=ROW(B3:D6)-ROW(INDEX(B3:D6,1,1))+1…
  • Get the First Row Number in a Range
    If the ROW function use a Range as its argument, it only returns the first row number.You can also use the ROW function within the MIN function to get the first row number in a range. You can also use the INDEX function to get the reference of the first row in a range, then combined to the ROW function to get the first row number of a range.…
  •  Get the Last Row Number in a Range
    If you want to get the last row number in a range, you need to know the first row number and the total rows number of a range, then perform the addition operation, then subtract 1, the last result is the last row number for that range.…

5. Related Functions

  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel Substitute function
    The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string. The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num])….
  • Excel Find function
    The Excel FIND function returns the position of the first text string (substring) from the first character of the second text string. The FIND function is a build-in function in Microsoft Excel and it is categorized as a Text Function. = FIND(find_text, within_text,[start_num])…

How to extract text after first comma or space

In the previous post, we talked that how to extract substring before the first comma or space or others specific characters in excel. And this post will guide you how to extract text after the first comma or space character in a text string using a formula and VBA code..

1. Extract Text after First Comma or Space Using Formula

If you want to get substring after the first comma character from a text string in Cell B1, then you can create a formula based on the MID function and FIND function or SEARCH function as follows:

=MID(B1,FIND(",",B1)+1,LEN(B1))

or

=MID(B1,SEARCH(",",B1)+1,LEN(B1))

Let’s see how this formula works:

=LEN(B1)

The LEN function returns the number of characters in a text string in Cell B1. The returned result goes into the MID function as its num_chars argument.

=FIND(“,”,B1)+1

extract text after first comma11

The FIND function returns the position of the first comma character in Cell B1. It returns 7. And then add 1 to get the position of the first character after comma character. The returned value goes into the MID function as its start_num argument.

=MID(B1,FIND(“,”,B1)+1,LEN(B1))

extract text after first comma1

So far, you got the values of the start_num and num_chars arguments from above FIND and LEN formula. And then the MID function extracts a substring based on the starting position and the number of the characters that you want to extract from a text string in Cell B1.

Last, if you want to extract a string after the first space character or others specific characters in a text string in cell B2, then you just need to change the comma character to space character in the above MID function, like this:

=MID(B1,FIND(" ",B1)+1,LEN(B1))
extract text after first comma2

2. Extract Text after First Comma or Space using a User Defined Function with VBA Code

You can use the following VBA code to create a user-defined function in Excel that extracts text after the first comma or space in a cell. Just do the following steps:

Step1: Press Alt + F11 to open the VBA editor in your current worksheet.

Adding Comma Character at End of Cells vba1.png

Step2: In the VBA editor, go to Insert -> Module to create a new module.

Adding Comma Character at End of Cells vba1.png

Step3: Paste the below code into the module. Save the module and close the editor.

How to extract text after first comma or space vba 1.png
Function ExtractTextAfterCommaOrSpace_Excelhow(text As String) As String
    Dim pos As Integer
    pos = InStr(text, ",")
    If pos = 0 Then
        pos = InStr(text, " ")
    End If
    If pos = 0 Then
        ExtractTextAfterCommaOrSpace_Excelhow = ""
    Else
        ExtractTextAfterCommaOrSpace_Excelhow = Trim(Mid(text, pos + 1))
    End If
End Function

Step4: Go back to Excel and enter a cell where you want to use the function. Type the following formula:

=ExtractTextAfterCommaOrSpace_Excelhow(B1)

Step5: press Enter to apply this formula. And the extracted text would be returned.

How to extract text after first comma or space vba 2.png

3. Video: Extract Text after First Comma or Space

This video will demonstrate how to extract text after the first comma or space in Excel using both a formula and VBA code.

4. Related Formulas

  • Extract Text between Parentheses
    If you want to extract text between parentheses in a cell, then you can use the search function within the MID function to create a new excel formula…
  • Extract Text between Brackets
    If you want to extract text between brackets in a cell, you need to create a formula based on the SEARCH function and the MID function….
  • Extract Text between Commas
    To extract text between commas in Cell B1, you can use the following formula based on the SUBSTITUTE function, the MID function and the REPT function…..
  • Extract word that starting with a specific character
    Assuming that you have a text string that contains email address in Cell B1, and if you want to extract word that begins with a specific character “@” sign, you can use a combination with the TRIM function, the LEFT function, the SUBSTITUTE function ….
  • Extract text before first comma or space
    If you want to extract text before the first comma or space character in cell B1, you can use a combination of the LEFT function and FIND function….

5. Related Functions

  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)….
  • Excel FIND function
    The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
  • Excel SEARCH function
    The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH  (find_text, within_text,[start_num])…
  • Excel LEN function
    The Excel LEN function returns the length of a text string (the number of characters in a text string).The LEN function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEN function is as below:= LEN(text)…

How to get first word from text string

This post will guide you on how to extract the first word from a text string in Excel. Sometimes, you may have a long string of text and only need the first word for analysis or sorting purposes. Fortunately, there is a simple formula that can do this, or you can use VBA code to create a more powerful and flexible solution.

 In this post, we will explore both methods so that you can choose the one that best suits your needs.

1. Get First Word using Formula in Excel

If you want to extract the first word from a text string in a cell, you can use a combination of the IF function, the ISERR function, the LEFT function and the FIND function to create a complex excel formula.

You can use the LEFT function to extract the first word, but the problem is that how to get the length of the first word in the text.  So you can use the FIND function to get the position of the first space character in the text in a cell, the number is then subtracted by 1 to get the length of the first word.

So Assuming that you want to get the first word in a text string in Cell B1, then you can write down an excel formula as follows:

=IF(ISERR(FIND(" ",B1)),B1, LEFT(B1, FIND(" ",B1)-1))

Let’s see how this formula works:

=FIND(” “,B1)-1

get first word1

This formula returns the length of the first word in the text in Cell B1. It goes into the LEFT function as its num_chars argument.

=LEFT(B1, FIND(” “,B1)-1)

get first word1

This formula extracts the first word in the text in cell B1. The LEFT function extracts the left-most characters and the number of characters that you need to extract is specified by the result returned by the FIND function.

= ISERR(FIND(” “,B1))

get first word3

The ISERR function will check if the FIND function do not find any space character in the text, it means that it contains only one word or a blank Cell.

=IF(ISERR(FIND(” “,B1)),B1, LEFT(B1, FIND(” “,B1)-1))

get first word4

If the ISERR function returns TRUE, then returns the value of Cell B1, otherwise, returns the first word of the text in Cell B1.

2. Get First Word using User Defined Function with VBA Code

You can create a User Defined Function in VBA code to extract the first word from a text string in Excel, here are the steps:

Step1: Open the Visual Basic Editor by pressing Alt + F11.

Adding Comma Character at End of Cells vba1.png

Step2: In the VBA Editor, go to Insert > Module to create a new module.

Adding Comma Character at End of Cells vba1.png

Step3: In the new module, enter the following code. Save the module and close the VBA Editor.

How to get first word from text string vba 1.png
Function GetFirstWord_Excelhow(str As String) As String
    Dim arr() As String
    arr = Split(str, " ")
    GetFirstWord_Excelhow = arr(0)
End Function

Step4: Go back to your Excel worksheet and enter a text string in a cell.

Step5: In a blank cell, enter the following formula:

=GetFirstWord_Excelhow (B1)

 Where B1 is the cell containing the text string.

Step6: Press Enter to see the first word extracted from the text string.

How to get first word from text string vba 2.png

The function splits the input string into an array of words using the space character as the delimiter, and returns the first word in the array using the index 0.

3. Video: Get First Word from Text String

This video will show you how to extract the first word from a text string in Excel using either a formula or VBA code.

4. Related Formulas

  • Split Multiple Lines from a Cell into Rows
    If you have multiple lines in a cell and each line is separated by line break character or press “alt + enter” key while you entering the text string into cells, and you need to extract the multiple lines into the separated rows or columns, you can use a combination with the TRIM function, the MID function, the SUBSTITUTE function, the REPT function, the LEN function to create a complex excel formula..…
  • Extract nth word from text string
    If you want to extract the nth word from a text string in a single cell, you can create an excel formula based on the TRIM function, the MID function, the SUBSTITUTE function, the REPT function and the LEN function..…
  • Get last word from text string
    If you want to get the last word from a text string, you can create an excel formula based on the RIGHT function, the LEN function, the FIND function and the SUBSTITUTE function..…
  • count specific words in a cell or a range
    If you want to count the number of a specific word in a single cell, you need to use the SUBSTITUTE function to remove all that certain word in text string, then using LEN function to calculate the length of the substring that without that specific word.…
  • Extract word that starting with a specific character
    Assuming that you have a text string that contains email address in Cell B1, and if you want to extract word that begins with a specific character “@” sign, you can use a combination with the TRIM function, the LEFT function, the SUBSTITUTE function, the MID function, the FIND function, the LEN function and the REPT function to create an excel formula.…

5. Related Functions

  • Excel LEFT function
    The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel FIND function
    The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
  • Excel ISERR function
    The Excel ISERR function returns TRUE if the value is any error value except #N/A.The syntax of the ISERR function is as below:= ISERR (value)…

How to Find the Position of First Number in A Text String in Excel

This post will guide you how to get the position of the first number in a text string in Excel. How do I find the position of first number in a text string in Excel 2013/2016.

1. Find the Position of First Number in a Cell using Formula

Assuming that you have a list of data in range B1:B4, in which contain text strings. And you wish to get the position of the first number in those range of cells. How to accomplish it. And you can use an formula based on the MIN function and the SEARCH function. Like below:

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B1&"0123456789"))

Type this formula into a blank cell and press Enter key on your keyboard, and drag the AutoFill Handle down to other cells to apply this formula.

find first position of first number in cell1

You would see that the position of the first number is calculated.

If you want to get the position of last number in a text string in your range of cells, you can use another array formula based on the MAX function, the IFERROR function, the FIND function, the ROW function, and the LEN function. Like this:

=MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},B1,ROW(INDIRECT("1:"&LEN(B1)))),0))

Type this formula into a blank cell and press Ctrl + Shift + Enter keys on your keyboard, and drag the AutoFill Handle down to other cells to apply this formula.

find first position of first number in cell2

2. Find the Position of First Number in a Cell using User Defined Function with VBA Code

You can also create a User defined function with VBA Code to find the position of first number in a given cell in Excel. here are the steps:

Step1: press Alt + F11 to open the Visual Basic Editor.

Adding Comma Character at End of Cells vba1.png

Step2: In the Visual Basic Editor, click Insert > Module to insert a new module.

Adding Comma Character at End of Cells vba1.png

Step3: Copy the below VBA code for the custom function and paste it into the new module. Save the VBA module by clicking File > Save or by pressing Ctrl + S.

How to Find the Position of First Number in A Text String in Excel vba 1.png
Function FirstNumberPosition_Excelhow(rng As Range) As Integer

Dim s As String
Dim i As Integer

s = rng.Value

For i = 1 To Len(s)
    If IsNumeric(Mid(s, i, 1)) Then
        FirstNumberPosition_Excelhow = i
        Exit Function
    End If
Next i

End Function

Step4: Type the following formula in a blank cell:

=FirstNumberPosition_Excelhow(B1)

Step5: Press Enter to calculate the result of the formula using the custom function.

How to Find the Position of First Number in A Text String in Excel vba 2.png

3. Video: Find the Position of First Number in a Cell

This video will demonstrate how to find the position of the first number in a cell using a formula or VBA code in Excel.

How to Get Text before or after Dash Character in Excel

This post will guide you how to get text before or after dash character in a given cell in Excel. How do I extract text string before or after dash character in Excel.

1. Video: Get Text before or after Dash Character in Excel

You can watch this short video tutorial to learn how to easily extract text before or after a dash character in Excel using simple functions.

2. Get Text before Dash Character with Formula

Assuming that you have a list of data in range B1:B5, which contain text string. And you want to extract text before or after dash character in a given cell or range. How to do it. You can use a formula based on the LEFT function and the FIND function to extract text before dash character in a given cell in Excel. Like this:

=LEFT(B2,FIND("-",B2)-1)

Select the cell where you want to display the result, then type this formula into a blank cell and press Enter key on your keyboard, and drag the AutoFill handle down to other cells to apply this formula to extract text before dash.

get text before dash1

You would notice that all text values before dash character have been extracted into new cells.

3. Get Text after Dash Character with Formula

If you want to get text values after dash character from a given cell or range in Excel, you can use another formula based on the REPLACE function and the Find function. Like this:

=REPLACE(B2,1,FIND("-",B2),"")

Type this formula into a blank cell and press Enter key on your keyboard, and drag the AutoFill handle down to other cells to apply this formula to extract text after dash.

get text before dash2

Let’s see how this formula works:

The Find function will return the position of the first dash character from text string in Cell B1, and pass the returned value to REPLACE function as its second argument.

The REPLACE function will replace all text string from the first character to the first dash character. So that you can get text string after dash character.

How to Get Text before or after Dash Character in Excel 20.png

To avoid this problem, you can use the IFERROR function in combination with the above formula to display a blank cell instead. 

=IFERROR(REPLACE(B2,1,FIND("-",B2),""),"")
How to Get Text before or after Dash Character in Excel 21.png

4. Get Text Values before and after Dash Character with Text to Column

You can also use Text to Column feature to achieve the same result of extracting text string before and after dash character from the selected range of cells in Excel. Here are the steps:

Step1: select your data that you want to split text string by dash.

get text before dash3

Step2: go to DATA tab, click Text to Columns command under Data Tools group. And the Convert Text to columns Wizard dialog will open.

get text before dash4

Step3: choose Delimited radio button under Original data type section. Click OK button.

get text before dash5

Step4: only check Other check box under Delimiters section, and enter dash (-) character into the Other text box, and click Next button.

get text before dash6

Step5: select one Destination cell to place text strings or just click Finish button.

get text before dash7
get text before dash8

You would notice that the selected cells have been split into two columns by dash character. And one column contain the text string before dash from the original text string. And another column contain the text string after dash character.

5. Related Functions

  • Excel Replace function
    The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day) …
  • Excel LEFT function
    The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
  • Excel FIND function
    The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…

If Cell Contain Specific Text

Cell contains specific tex

This article will explain how to check if a cell contains text or a specific text string in Google Sheets or Microsoft Excel spreadsheets. In our daily work, we often look for specific text in worksheets containing large amounts of data or need to determine if a specific cell area contains text values.

We may also encounter the need to find out if a cell contains partial text or we need to find a specific text string in a cell by using VBA code.

Cell Contains Specific Text in Excel & Google Sheets

Below we will show how to check if a cell contains a specific text by using a formula in Excel or Google Sheets.

If you want to check if a given cell contains a specific text, then you can use the SEARCH function in combination with the ISNUMBER function to create a nested formula. You can refer to this general formula below.

=ISNUMBER(SEARCH(specific_text,text))

In the general formula above, specific_text is the specific text you want to find, and text is the text string or cell reference you want to find that contains the specific text.

If you found a specific text, then the formula will return TRUE, otherwise it will return FALSE.

If you need to find partial text from the cell, then the SEARCH function is very suitable; by default, the SEARCH function will automatically find partial text to match.

For the example in this article, you can use the following formula to find specific text.

=ISNUMBER(SEARCH(B4,A4))
Cell contains specific text

From the screenshot above, we can see that the formula supports partial text matching, where specific_text is “rest” and the text is “interest dividends“, and the result returned is TRUE, which means that partial text matching for a specific character is successful.

Let’s see how this formula works:

When the SEARCH function finds a specific text, it returns the position of that text in the searched text cell, and if not found, it returns #VALUE! . So, we can take advantage of this feature by simply using the ISNUMBER function to check if the SEARCH function returns a number, and if it is a number, it means that the specific text was found in the cell.

For the ISNUMBER function, it returns TRUE if the given argument is a number, otherwise it returns FALSE. therefore, when the SEARCH function can return the position of a specific character, then the ISNUMBER function returns TRUE. if the SEARCH function returns a #VALUE error, then the ISNUMBER function will return FALSE.

Cell Contains Specific Text with Case Sensitive in Excel & Google Sheets

You can use the SEARCH function to find specific text and partial text matches, but the function is not case-sensitive.

If you want to check for specific text in a case-sensitive manner in a text cell, then you cannot use the SEARCH function, and you need to replace the SEARCH function with the FIND function, using the following formula.

=ISNUMBER(FIND(B4,A4))
Cell contains specific text

As you can see from the above figure, specific_text “Rest” can no longer be successfully matched in text cell A4.

If Cell Contains Specific Text Then in Excel & Google Sheets

If you want to do something else when you find a cell contains a specific text, for example, we do not want to return TRUE or FALSE results, but rather return some more meaningful text or results.

You can build an IF nested formula by nesting the above formula into the IF function as follows.

=IF(ISNUMBER(SEARCH(B4,A4)),"Found", "No Found")
Cell contains specific text

Note: Because this formula uses the SEARCH formula, the result is case-insensitive.

Sum If Cell Contains Text in Google sheets & Excel

If a column of cells contains a specific text string, you want to add the corresponding cell value of another column, then you can use the SUMIFS function contains a specific text of the cells corresponding to the data of another column to sum. The general formula is as follows.

=SUMIFS(sum_range, criteria_range,specific_text)

If you want to find all the cells in the cell region A4:A12 contains the “expen” string, and the corresponding value of another column B4:B12 to sum, then you can use the following formula.

=SUMIFS(B4:B12,A4:A12, "*expen*")
Cell contains specific text

If you are using google sheets, then you can also accomplish this task by using the following formula, which is as follows.

=SUM(filter(B4:B12, regexmatch(A4:A12,"expen")))
Cell contains specific text

As you can see from the chart above, the formula consisting of the SUM function and the FILTER function returns the same result as the EXCEL formula.

Note: Excel does not have a special wildcard formula that uses the “*” meta-character to match any character. There is a special regular expression function regexmatch in google sheet.

Conditional Formatting If Cell Contains in Google Sheets & Excel

In Microsoft Excel, if you want to highlight a text cell containing the “expense” string, then you can refer to the following steps.

STEP 1: Select the cell range A4:A12, then in the HOME tab, click the Conditional Formatting button, and then click the New Rule submenu.

Cell contains specific text

STEP2: The New Formatting Rule window will pop up, click on “Use a formula to determine which cells to format“, then enter the following formula in the text box.

=SEARCH(“expense”,A4)
Cell contains specific text

STEP3: Click the Format button and select a color to fill the cells that can be matched successfully. Click OK.

Cell contains specific text

STEP 4: You will see that all cells that contain the expense string are automatically highlighted.

Cell contains specific text

In google sheets, if you want to highlight a text cell that contains an “expense” string, then you can refer to the following steps.

STEP1: Select the cell range that contains a specific text to be highlighted by conditional formatting

Cell contains specific text

STEP 2: Click Format menu, then click Conditional Formatting submenu, the “Conditional format rules” window will pop up on the right side of the page.

Cell contains specific text

STEP 3: Select Text Contains in the Format rules drop-down list and enter the specific text string “expense” in the text box

Cell contains specific text

STEP 4: Select a highlight color in Formatting style and click the Done button. 

Cell contains specific text

STEP 5: Let’s see the final result.

Cell contains specific text

Related Functions

  •  Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
  • Excel SUM function
    The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
  • Excel SEARCH function
    The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH  (find_text, within_text,[start_num])…
  • Excel Find function
    The Excel FIND function returns the position of the first text string (substring) from the first character of the second text string.The FIND function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the FIND function is as below:= FIND  (find_text, within_text,[start_num])…
  • Excel SUMIFS Function
    The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…
  • Excel Filter function
    The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…

Cell Contains Number

check Cell Contains Number

This article will talk about how to check if a given cell contains any numbers in Microsoft Excel Spreadsheet or Google Sheets. How to quickly check if a cell contains a number? When you have a small number of cells that need to filter out the cells that contain numbers, you can manually select them, and that is acceptable; however, when there are thousands of cells, it is not wise to filter the cells with numbers by manually.

Below we will see how to quickly check whether the cell contains numbers by Excel or google sheets formula, when the cell contains numbers, return TRUE, otherwise return FALSE.

Cell Contains Number in Google Sheets & Excel

In Excel or google sheets, if a cell contains letters and numbers, then we can assume that it is a text cell.

Generic Formula

If you need to test whether a given text cell contains any of the numbers, then you can use the FIND function in combination with the COUNT function consisting of nested formulas. The general formula is as follows.

=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A3))>0
check Cell Contains Number

where A3 is the text cell reference you want to test, the formula will go to check whether the text cell A3 contains the given number, and the number to be checked in the form of an array nested into the FIND formula.

Formula Explanation

= FIND({0,1,2,3,4,5,6,7,8,9},A3))

First, we need to use the FIND function in the given text cell to find out the location of the corresponding number in the text, if the number is found, then it will return the location of the number in the text string, if not found, then return # VALUE!

For example: the formula FIND (“2”, A3), the number 2 is in the position of 7 in cell A3, then the FIND function will return 7. Just see the below screenshot.

check Cell Contains Number

Since we pass the FIND function an array containing numbers, the FIND function will return a similar array as the return value.

{8,9,7,10,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}
check Cell Contains Number

Note: You can also replace the FIND function with the SEARCH function to find the position of a number in a text string, so the SEARCH function can return the same result.

=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A3))

The array result returned by the FIND formula is passed to the COUNT function as an argument, and the COUNT function counts the items in the array. Since the COUNT function only counts numeric values, all # VALUE! items in the array will be considered as 0.

When there are no numeric items in the array, the COUNT formula will return 0, otherwise it will return a value greater than 0.

For example: we use COUNT formula to count the array values {1,3,#VALUE!}, it returns 2, see the screenshot below:

check Cell Contains Number

=COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A3))>0

When we count the number of numbers contained in the text cell, then we need to compare the number of numbers is greater than 0, if greater than 0, then return TRUE, if equal to 0, then return FALSE.

We just need to compare the value returned by the COUNT formula with 0, see the below example:

=COUNT({1,2,#VALUE!})>0
check Cell Contains Number

As you can see from the screenshot above, the COUNT function returns 2, which is greater than 0, so it returns TRUE.

IF Nested Formulas

If you want to make the return value look more meaningful, then you can embed the COUNT formula above into the IF function so that it returns YES or No, see the following formula.

=IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A3))>0,"yes","NO")
check Cell Contains Number

Check Cell Contains Number in Google Sheets

You can use the generic formula above in Google Sheets to check if a text cell contains a number.

check Cell Contains Number

Note: Now you can pass array values directly in Google sheet, so you don’t need to use the CTRL+SHIFT+ENTER shortcut to turn the formula into an array formula. Of course, you can use the shortcut to convert it to an array formula and the result is the same.

=ARRAYFORMULA(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A3))>0)

See the below screenshot:

check Cell Contains Number

You can see that the returned value is the same as the formula above.

Related Functions

  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel FIND function
    The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
  • Excel COUNT function
    The Excel COUNT function counts the number of cells that contain numbers, and counts numbers within the list of arguments. It returns a numeric value that indicate the number of cells that contain numbers in a range…

Count Cells that are Case Sensitive

If you are a frequent user of Microsoft Excel, you may have come across situations where you needed to add or count the cells that were case-sensitive. If you have done this task manually, I would like to point out that it is the most inefficient way to do it, particularly when MS Excel has the versatility to do it in a matter of seconds. As a result of attentively reading this article, you will learn about the many adding or counting case-sensitive cells.

count cell that contain case sensitive1

Simple generic formula:

=SUMPRODUCT(--ISNUMBER(FIND(value, range)))

Summary

You may use a formula depending on the ISNUMBER and FIND functions and the SUMPRODUCT function to calculate the number of cells that contain specified text (i.e., hold a substring), taking into consideration upper and lower case. D1 in the example displayed has the following formula, which has been written down:

=SUMPRODUCT(--ISNUMBER(FIND(C1,$A$1:$A$6)))

count cell that contain case sensitive1

Let’s See How This Formula Works

In this example, the purpose is to count the number of times each code appears as a substring in a case-sensitive manner, as shown below. When calculating text values, the functions COUNTIF and COUNTIFS are excellent choices. However, since these functions are not case-sensitive, they cannot be utilized to address this issue. To check for substrings, the FIND function should be used in conjunction with the ISNUMBER function, and the SUMPRODUCT function should be used to sum up, the results.

= FIND(C1,$A$1:$A$6)

count cell that contain case sensitive1

The FIND function is case-sensitive and accepts three parameters: find text, within the text, and start num. It is also case-sensitive and takes three arguments. Here is the text we are searching for, and this is the text that we are searching inside. Start num is the number of characters to begin searching in find text. Because start num defaults to 1, we are not supplying a number in this instance. This is necessary since we always want FIND to begin with the first character. When finding the text is discovered inside within text, FIND returns the position of the found text as a number: When finding the text is located within text, FIND returns the position of the found text as a number:

Note: When the find text function fails to yield a result, FIND returns the #VALUE! Error.

=ISNUMBER(FIND(C1,$A$1:$A$6))

count cell that contain case sensitive1

This implies that we may be using the ISNUMBER function to transform the output of the FIND function into a TRUE or FALSE value using the ISNUMBER function. A TRUE value will arise from any number, while a FALSE value will result from any error:

As demonstrated in the example, we have four substrings in column C and a variety of codes in columns A1:A6, representing the data for the designated range. Specifically, we want to know how many times each substring in C1:C4 occurs in A1:A6, and we want to know how many times each substring is case-sensitive.

Related Functions

  • Excel SUMPRODUCT function
    The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
  • Excel COUNTIFS function
    The Excel COUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)…
  • Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
  • Excel FIND function
    The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…

Abbreviate Names Or Words in Excel

As an MS Excel user, you might have come across a task where you need to abbreviate different names or words, and there are also possibilities that you might have done this task manually by assuming that there isn’t any other way to do this task, but you assumed wrong because fortunately there is a way which would let you abbreviate names and words in a matter of seconds unlike doing it manually which consumes a lot of time and leaves you with no satisfying results.

abbreviate names1

So for exploring that way/method, let’s dive into the article.

General Formula:

The array formula to abbreviate different names and words in few seconds is mentionedas follows:

{=TEXTJOIN("",1,IF(ISNUMBER(MATCH(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),ROW(INDIRECT("65:90")),0)),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""))}

abbreviate names1

Syntax Explanation:

To understand the working of the above formula, we first need to know about each syntax and how they contribute to abbreviating names and words in seconds.

  • TEXTJOIN: The TEXTJOIN function in Microsoft Excel allows you to join two or more strings together, with each value separated by a delimiter. The TEXTJOIN function is an Excel built-in function classified as a String/Text Function.
  • IF: The IF function is one of Excel’s most used functions, allowing you to create logical comparisons between a number and what you anticipate.
  • ISNUMBER: When a cell contains a number, the ISNUMBER function returns TRUE; otherwise, it returns FALSE. ISNUMBER can be used to verify that a cell contains a numeric value or that the output of another function is a number.
  • MATCH: The MATCH is a function used to find a lookup value in a row, column, or table in MS Excel. MATCH allows for approximate and accurate matching and wildcards (*?) for partial matches.
  • CODE: The CODE function in Excel returns a numeric code for a specified character.
  • INDIRECT: The INDIRECT function returns a range reference. This function may generate a reference that will not change if a row or column is added to the worksheet.

Let’s See How This Formula Works:

To abbreviate capital letters text, use this array formula based on the TEXTJOIN function, new in Office 365 and Excel 2019. You may use this method to generate initials from names or acronyms. Because only capital letters can survive this algorithm, the original text must contain capitalized terms. If necessary, you can capitalize words using the PROPER function.

The formula in B2 in the case provided is:

=TEXTJOIN("",1,IF(ISNUMBER(MATCH(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),ROW(INDIRECT("65:90")),0)),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),""))

The MID function is used to turn the string into an array of individual letters from the inside out:

=MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)

In this formula section, the operators MID, ROW, INDIRECT, and LEN transform a string into an array of letters.

abbreviate names1

MID delivers an array of all characters in the text.

=CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))

This above array is sent to the CODE function, which returns an array of numeric ASCII codes, one for each letter.

abbreviate names1

=ROW(INDIRECT("65:90")

ROW and INDIRECT are used to generate another numeric array:

{65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84;85;86;87;88;89;90}

abbreviate names1

Note: The digits 65 to 90 correspond to the ASCII codes for all capital letters from A to Z. This array is used as the lookup array in the MATCH function, and the original array of ASCII codes is given as the lookup value.

{=MATCH(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),ROW(INDIRECT("65:90")),0)}

abbreviate names1

The MATCH function returns either a number or the #N/A error. Because numbers represent capital letters.

{=IF(ISNUMBER(MATCH(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),ROW(INDIRECT("65:90")),0)),MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1),"")}

abbreviate names1

The ISNUMBER function is used with the IF function to filter results. Only characters with ASCII codes between 65 and 90 will be included in the final array, reconstructed using the TEXTJOIN method to produce the final abbreviation or acronym.

Abbreviate Names And Words In MS Excel 2016 Or Older Versions

As in Excel 2016 and earlier versions, the formula discussed above would not work, so the TRIM function is available in Excel 2016 and earlier versions, so we use it to abbreviate names and words.

abbreviate names1

General Formula:

The formula we would use in MS Excel 2016 and earlier versions to abbreviate names and words as follows.

=TRIM(LEFT(Text,1)&MID(Text,FIND(" ",Text&" ")+1,1)&MID(Text,FIND("*",SUBSTITUTE(Text&" ","*,"2))+1,1)

How Does This Formula Work?

From cell A2, if you want to extract the initials, enter this formula in cell B2.

=TRIM(LEFT(A2,1)&MID(A2,FIND(" ",A2&" ")+1,1)&MID(A2,FIND("*",SUBSTITUTE(A2&" "," ","*",2))+1,1))

Here the text string is the string you want to extract the first letters of each word.

When you press the Enter key, all of the first letters of each word in cell A2 are retrieved.

abbreviate names1

Explanation:

  1. The TRIM function eliminates any extra spaces from the text string.
  2. The LEFT(A2,1) function retrieves the first letter of the text string.
  3. MID(A2,FIND(” “,A2&” “)+1,1) retrieves the initial letter of the second word separated by a space.
  4. MID(A2,FIND(“*”,SUBSTITUTE(A2&” “,” “,”*,”2))+1,1) retrieves the initial letter of the third word separated by a space.

NOTE:

This formula only works when three or fewer words are in a cell. You can modify ” “ in the formula to different delimiters.

This formula extracts the initial characters in a case-insensitive manner; if you want the formula to always return in the upper case, include the UPPER function in the formula.

=UPPER(TRIM(LEFT(A2,1)&MID(A2,FIND(" ",A2&" ")+1,1)&MID(A2,FIND("*",SUBSTITUTE(A2&" "," ","*",2))+1,1)))

abbreviate names1

Related Functions

  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel TEXTJOIN function
    The Excel TEXTJOIN function joins two or more text strings together and separated by a delimiter. you can select an entire range of cell references to be combined in excel 2016.The syntax of the TEXTJOIN function is as below:= TEXTJOIN  (delimiter, ignore_empty,text1,[text2])…
  • Excel ROW function
    The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…
  • Excel INDIRECT function
    The Excel INDIRECT function returns the cell reference based on a text string, such as: type the text string “A2” in B1 cell, it just a text string, so you can use INDIRECT function to convert text string as cell reference….
  • Excel MATCH  function
    The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
  • Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
  • Excel CODE function
    The Excel CODE function returns the numeric ASCII value for the first character of a text string.The syntax of the CODE function is as below:= CODE  (text)…
  • Excel LEN function
    The Excel LEN function returns the length of a text string (the number of characters in a text string).The LEN function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEN function is as below:= LEN(text)…
  • Excel LEFT function
    The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
  • Excel FIND function
    The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
  • Excel TRIM function
    The Excel TRIM function removes all spaces from text string except for single spaces between words.  You can use the TRIM function to remove extra spaces between words in a string.The syntax of the TRIM function is as below:= TRIM (text)….

 

 

Extract Last Two Words From Multiple Cells

Just assume that you have a few cells containing values/words and you want to extract the last two words from each cell into another separate cell; then you might think that it’s not a big deal; because you would prefer to manually extract the last two words from the cell into another without any need of the formula then congratulations because you are thinking right, but let me add up that it would be a big deal to extract the last two words from the multiple cells to another cell and doing it manually would be a foolish attempt because you would get tired of it and would never complete your work on time.

But don’t be worry about it because after carefully reading this article, extracting last the two words from multiple cells into separate cells would become a piece of cake for you.

So let’s dive into the article to take you out of this fix.

General formula:

The Following formula would help you out for extracting last the two words from multiple cells into separate cells :

=MID(B1,FIND("#",SUBSTITUTE(B1," ","#",LEN(B1)-LEN(SUBSTITUTE(B1," ",""))-1))+1,200)

extract last word from multiple cells1

Syntax Explanations:

Before going into the explanation of the formula for getting the work done efficiently, we must understand each syntax which would make it easy for you that how each syntax contributes to extracting the last two words/values from multiple cells into separate cells:

  • MID: This function contributes to extracting the number or characters from the given string by starting from the left side.
  • FIND: In Excel, this FIND function contributes to finding out one text inside the other one.
  • LEN: In Excel, this LEN function contributes to finding out the length of the text string.
  • SUBSTITUTE: In excel, this SUBSTITUTE function contributes to replacing the existing text with new text in a text string.
  • B1: this function represents the input value.
  • Comma symbol (,): In Excel, this comma symbol acts as a separator that helps to separate a list of values.
  • Parenthesis (): The core purpose of this Parenthesis symbol is to group the elements and to separate them from the rest of the elements.
  • Minus Operator (-): This minus symbol contributes to subtracting any two values.
  • Plus operator (+): This plus symbol adds the values.

Let’s See How This Formula Works:

The formula uses the MID function to extract the characters from the second to last space. The MID function accepts three arguments; the number of characters to extract, the starting position, the text to work with.

The text is from column B, and the number of characters can be large enough to ensure that the last two words are taken. The task is to figure out where to start, just after the second-to-last spot. The sophisticated work is mostly done with the SUBSTITUTE function, which accepts an optional input called instance number. This function is used to replace the second to last space in the text with the “#” character, which is then found using the FIND function.

The following snippet figures out how many total spaces are in the text, from which 1 is subtracted.

=LEN(B1)-LEN(SUBSTITUTE(B1," ","")-1

extract last word from multiple cells1

According to the example, the above code would return 5 because there are 6 spaces in the text. As the instance number, this returning number is then placed into the SUBSTITUTE function.

=SUBSTITUTE(B1," ","#",5)

extract last word from multiple cells1

Due to the above placement of the returning number, the SUBSTITUTE function would replace the fifth space character with “#“, now you might be curious about it that why we are using “#” ? so here is the answer that it’s an arbitrary choice you can use any other character too but here is the condition that the chosen character would not appear in the original text.

After this, the FIND Function would locate the “#” character (or whatever character you would use) in the text:

=FIND("#","Extract Multiple Match Values into#Separate Columns")

extract last word from multiple cells1According to the example, the result of the FIND function would be 35, in which 1 is added for getting 36. This is the starting point, and as the second argument, it would go into the MID function.

Extract Last N words from String Using MID Function

In this formula, we have made the adjustments to extract the last 2 words from the cells and can also be generalized to extract the last N words from a cell by replacing the hardcoded 1 in the example with (N-1).

Moreover, if you want to extract many words, you must replace the hardcoded argument in MID, 200, with a larger number. To make sure that the number is large enough, you can use the LEN function, like it is used as follows:

=MID(B1,FIND(“#”,SUBSTITUTE(B1,” “,”#”,LEN(B1)-LEN(SUBSTITUTE(B1,” “,””))-1))+1,LEN(B1))

extract last word from multiple cells1

Related Functions

  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…
  • Excel Substitute function
    The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string. The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num])….
  • Excel LEN function
    The Excel LEN function returns the length of a text string (the number of characters in a text string).The LEN function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEN function is as below:= LEN(text)…
  • Excel FIND function
    The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])

How to Count Cells that Contain X or Y in Excel

This post will guide you how to count the number of cells that contain X or Y in a given range cells using a formula in Excel 2013/2016.You can easily to count cells equal to a specific value or text string through COUNTIF function. But if there is an easy way to count cells contain either one value or another value in a selected range of cells in Excel.

In the previous post, we talked that how to count cells that equal to either x or y in a given range with two COUNTIF functions. And it will double count cells that contain both X and Y. So it may be not your expected result, and you may only want to add it once while cells that contain both X or Y. the below steps will teach you how to count cells that contain either X or Y(do not double count).

Count Cells Contain X or Y

Assuming that you want to count cells that contain value “excel”or “word” defined in a selected range(A1:B6).In this case, you can use the SUMPRODUCT function combining with ISNUMBER function and FIND function to count cells which are contain either  X or Y.

Enter the following formula in a blank cell, and press Enter key:

=SUMPRODUCT(–((ISNUMBER(FIND(“excel”,A1:B6)) +  ISNUMBER(FIND(“word”,A1:B6)))>0))

count cell that contain x or y1

Note: A2:A6 is the data range that you want to use. And both“excel” and “word” are the text values that you want to count in range A2:A6. You need to change them as you need.

NOW LET’S SEE HOW THIS FORMULA WORKS:

=FIND(“excel”,A1:B6)

count cell that contain x or y2

The FIND function can be used to find the position of text “excel” or “word” in range A1:B6, and it returns a array result contain 1 or #VALUE error. When the text “excel” or “word” is not found, returns a #VALUE error. The returned results for the above formula is like below:

={#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!}

count cell that contain x or y3

You can see that there are two “1” value, it means that there are two cells that contain “excel”text string.

=ISNUMBER(FIND(“excel”,A1:B6))

count cell that contain x or y4

Then you can use the ISNUMBER function to check the above array result, it will convert all numeric value as True, and convert other values to False.

={FALSE,FALSE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,FALSE}

count cell that contain x or y5

You still need to convert the above logic values to number “1” or “0” with double negative character like below:

=–ISNUMBER(FIND(“excel”,A1:B6))

={0,0;1,0;0,0;0,0;0,0;1,0}

count cell that contain x or y6

count cell that contain x or y7

Note: You can press “Fn” + “F9” to display the array result for the above array formula.

Related Functions

  • Excel SUMPRODUCT function
    The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products. The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
  • Excel Find function
    The Excel FIND function returns the position of the first text string (substring) from the first character of the second text string.The FIND function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the FIND function is as below:= FIND  (find_text, within_text,[start_num])…
  • Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…

How to Remove Middle Name from Full Name in Excel

When we get a list of full names, we can remove the middle name for short in some cases. As the middle names are different among the full name list so we cannot replace them by space directly in excel. This article will provide you two methods to remove the middle name. User can use Formula to remove the middle name, or can remove it by Find and Replace function.

Prepare a list of full names.

Remove Middle Name from Full Name 1

Remove the Middle Name from Full Name by Formula

Step1: Select a blank cell next to the full name, for example select B1. Enter the following formula into B1:

=TRIM(LEFT(A1,FIND(” “,LOWER(A1),1))) & ” ” & TRIM(MID(A1,FIND(” “,LOWER(A1),FIND(” “,LOWER(A1),1)+1)+1,LEN(A1)-FIND(” “,LOWER(A1),1)+1)) .

Remove Middle Name from Full Name 2

Step2: Click Enter and get the result.

Remove Middle Name from Full Name 3

Step3: Drag the Auto Fill handle to fill B2 and B3. Now all the middle names are removed from the list.

Remove Middle Name from Full Name 4

Remove the Middle Name from Full Name by Find and Replace Function

Step1: Click Ctrl+F to load Find and Replace window.

Step2: Under Replace tab, in Find what textbox enter ‘ * ‘ (a space + * + a space); in Replace with textbox enter ‘ ‘ (a space). Then click Replace All.

Remove Middle Name from Full Name 5

Step3: Check the result. All the middle names are removed.

Remove Middle Name from Full Name 6

Related Functions

  • Excel LEFT function
    The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
  • Excel FIND function
    The Excel FIND function returns the position of the first text string (sub string) within another text string.The syntax of the FIND function is as below:= FIND(find_text, within_text,[start_num])…
  • Excel TRIM function
    The Excel TRIM function removes all spaces from text string except for single spaces between words.  You can use the TRIM function to remove extra spaces between words in a string.The syntax of the TRIM function is as below:= TRIM (text)….
  • Excel LEN function
    The Excel LEN function returns the length of a text string (the number of characters in a text string).The syntax of the LEN function is as below:= LEN(text)…
  • Excel LOWER function
    The Excel LOWER function converts all characters in a text string to lowercase.The syntax of the LOWER function is as below:= LOWER  (text)…

How to Extract Initials From a Name in Excel

This post will guide you how to get initials from a given name using a formula in Excel. How do I extract initials from names in Excel 2013/2016.

Assuming that you have a list of data in range B1:B4, in which contain name values, and you want to extract initials from these names. How to accomplish it. this post will show you two methods to get initials.

Extract Initials from a Name Using a Formula

To extract initials from a given name in Excel, you can do this using a formula based on the LEFT function, the IF function, the ISNUMBER function and the MID function. Like this:

=LEFT(B1)&IF(ISNUMBER(FIND(” “, B1)),MID(B1,FIND(” “, B1)+1,1),””)&IF(ISNUMBER(FIND(” “, B1,FIND(” “, B1)+1)),MID(B1,FIND(” “, B1,FIND(” “, B1)+1)+1,1),””)

You need to type this formula into a blank cell and press Enter key on your keyboard, and then drag the AutoFill Handle down to other cells.

extract initials from a name1

 

Extract Initials from a Name Using User Defined Function

You can also define a User Defined function to extract initials from a given name in Excel. Just do the following steps:

Step1# open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

 Get the position of the nth using excel vba1

Step2#  then the “Visual Basic Editor” window will appear.

Step3# click “Insert” ->”Module” to create a new module.

convert column number to letter3

Step4# paste the below VBA code into the code window. Then clicking “Save” button.

Function GetFirstCharacters(pWorkRng As Range) As String
myValue = pWorkRng.Value
myStr = VBA.Split(Trim(myValue))
For i = 0 To UBound(myStr)
    myResult = myResult & VBA.Left(myStr(i), 1) & ""
Next
GetFirstCharacters = myResult
End Function

Step5# type the following formula into a blank cell and press Enter key in your keyboard.

=GetFirstCharacters(B1)

extract initials from a name2

Related Functions

  • Excel IF function
    The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
  • Excel LEFT function
    The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
  • Excel ISNUMBER function
    The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…

Get Workbook Path Only

This post will guide you how to get the current workbook path in Excel. How do I insert workbook path only into a cell with a formula in Excel.

Get workbook path with Document Location

You can get the workbook path from the document location feature, and copy the location to a cell. Do the following steps:

#1 go to File tab, and click Options menu from the popup menu list. And the Excel Options dialog will open.

#2 click Quick Access Toolbar option, and select All commands from the drop down list of the Choose commands from. And then select Document Location value, click Add button to add it into Quick Access Toolbar.

get workbook path1

#3 click OK button. The workbook path is displayed in the Quick Access toolbar. Press Ctrl +C to copy it.

get workbook path2

Get workbook path with Formula

You can also use a formula based on the LEFT function, the Cell function, and the FIND function to get workbook path only in Excel.  Just like this:

=LEFT(CELL("filename",B1),FIND("[",CELL("filename",B1))-1)

Type this formula into a cell and then press Enter key.

get workbook path3

Let’s see how this formula works:

The Cell function will be used to get the full name and path of the workbook file.

The Find function will return the location number of the first left square bracket.

The left function will extract the workbook path based on the number returned by the Find function.

Related Functions

    • Excel Find function
      The Excel FIND function returns the position of the first text string (substring) from the first character of the second text string.The FIND function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the FIND function is as below:= FIND  (find_text, within_text,[start_num])…
    • Excel LEFT function
      The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…
    • Excel CELL function
      The Excel CELL function returns information about the formatting, location, size, or contents of a cell.The syntax of the CELL function is as below:= CELL (info_type,[reference])…

 

Insert The File Path and Filename into Cell

This post will guide you how to insert the file path and filename into a cell in Excel. Or how to add a file path only in a specified cell with a formula in Excel. Or how to add the current workbook name into a cell in Excel. How do I insert the current worksheet name into a Cell with a formula.

Insert File Path and Filename into Cell

If you want to insert a file path and filename into a cell in your current worksheet, you can use the CELL function to create a formual. Just like this:

=CELL("filename")

Type this formula into a blank cell, and then press Enter key in your keyboard.

insert filepath filename in cell1

This fromula will return the entire path, filename, and tab name of the current worksheet.

If you want to strip out the brackets and the sheet name, you can use the following formula based on the SUBSTITUTE function, the LEFT function, the CELL function and the FIND function:

=SUBSTITUTE(LEFT(CELL("filename"),FIND("]",CELL("filename"))-1),"[","")

insert filepath filename in cell2

Insert Filename Only into Cell

If you only want to insert the workbook name into a cell, you can use a formula based on the MID function, the SEARCH function, and the CELL function. Just like this:

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

Type this formula into a blank cell and then press Enter key.

insert filepath filename in cell3

You will see this formula will return only the current workbook name in Cell.

Insert Filepath Only into Cell

If you just want to insert the File Path into a cell, you can create a formula based on the LEFT function, the Cell function,and the FIND function. Just like this:

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)

Type this formula into a blank cell and then press Enter key to apply this formula.

insert filepath filename in cell4

This formula will return only the File path of the current workbook.

Insert Worksheet Name into Cell

If you want to get the worksheet name only and then insert it into one cell, you can create a formula based on the RIGHT function, the LEN function, the CELL function and the FIND function. Just like this:

=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename"),1))

Type this formula into a blank cell and then press Enter key to apply this formula.

insert filepath filename in cell5

This formula will return only the current worksheet name in a cell.

Related Functions

  • Excel Find function
    The Excel FIND function returns the position of the first text string (substring) from the first character of the second text string.The FIND function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the FIND function is as below:= FIND  (find_text, within_text,[start_num])…
  • Excel MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…
  • Excel CELL function
    The Excel CELL function returns information about the formatting, location, size, or contents of a cell.The syntax of the CELL function is as below:= CELL (info_type,[reference])…
  • Excel Substitute function
    The Excel SUBSTITUTE function replaces a new text string for an old text string in a text string.The syntax of the SUBSTITUTE function is as below:= SUBSTITUTE  (text, old_text, new_text,[instance_num]) ….
  • Excel LEFT function
    The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…
  • Excel SEARCH function
    The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH  (find_text, within_text,[start_num])…
  • Excel LEN function
    The Excel LEN function returns the length of a text string (the number of characters in a text string).The LEN function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEN function is as below:= LEN(text)…
  • Excel RIGHT function
    The Excel RIGHT function returns a substring (a specified number of the characters) from a text string, starting from the rightmost character.The syntax of the RIGHT function is as below:= RIGHT (text,[num_chars])…