How to REPLACE any substrings or COUNT them in a string

Let's say we are dealing with the following string entered in cell A1:

"Examples of replacing or counting any string elements in a cell"

To replace e.g. the 3rd letter 'a' with 'A' in the string, we would use this formula:

=SUBSTITUTE($A$1,"a","A",3)

To count e.g. the number of SPACES in the string, the following formula can be used:

=LEN($A$1) -LEN(SUBSTITUTE($A$1," ",""))        (result = 10)

To count the number of substrings, such as e.g. "e", "in", "le" or "count" in the string, the following formulas can be used:

• =(LEN($A$1) -LEN(SUBSTITUTE($A$1,"e","")))/LEN("e")    (result = 6)
• =(LEN($A$1) -LEN(SUBSTITUTE($A$1,"in","")))/LEN("in")    (result = 4)
• =(LEN($A$1) -LEN(SUBSTITUTE($A$1,"le","")))/LEN("le")    (result = 2)
• =(LEN($A$1) -LEN(SUBSTITUTE($A$1,"count","")))/LEN("count")     (result = 1)

 

How to count any characters and digits in a text string using ARRAY formula

Let's say you need to find out how many characters, like e.g. a, or b, or w, or any other alphabetic character, or even digits, are there in a text string subject to your analysis.

Here is an example of a string located in cell A2:

Security guards at the hotel make sure guests remain in isolation, while police outside ensure no one enters the premises.

I want to count all characters (i.e. determine their frequency), from a to z (and A to Z) present within that string. This snip shows how easily it can be done using the ARRAY formula presented here:

How to find something with FIND function

While working in Excel we're always looking for something. The FIND function can be very helpful in our searches. 

Remember two things: it is case-sensitive (differentiates between "a" and "A") and doesn't work with date format (cells formatted as dates)

Used by itself, it just finds position of a character or text within a longer string. However, when used in combination with some other functions, it can be very useful in solving complex tasks. Here is summary of some examples of Find usage, described beneath the table.

Example 1: Find the directory/folder name of the file, you are using right now, with this formula:

=IF(CELL("Filename",A1)>"",LEFT(CELL("Filename",A1),FIND("[",CELL("Filename",A1))-1),"")

Example 2: Extract some number of characters (e.g. 4) following the second occurrence of "/":    =MID(A1,FIND("/",A1,FIND("/",A1,1)+1)+1,4)

Example 3: Return all characters between 2nd and 3rd "%":

=MID(A1,FIND("%",A1,FIND("%",A1)+1)+1,FIND("%",A1,FIND("%",A1,FIND("%",A1)+1)+2)-FIND("%",A1,FIND("%",A1,+1)-1)

Example 4: Find position of any of (2nd,3rd,4th...) occurrences of a given character in a string (e.g. 3rd occurrence of "e"):

=FIND(CHAR(10),SUBSTITUTE(A1,"e",CHAR(10),3))

Example 5: Find some substring of the string after a given character (e.g. after ">"):

=RIGHT(A1,LEN(A1)-FIND(">",A1))

Example 6: Find position of the second occurrence of some character in a string (e.g. "S"):

=FIND( "S",A1,FIND("S",A1,1)+1)

Example 7: Find text between two characters (can be the same or different); in this example, between "$" and "£": 

=MID(A1,FIND("$",A1)+1,FIND("£",A1)-FIND("$",A1)-1)

 

Pull number from alphanumeric string in a Cell

Sometimes you may need to pull the numbers from alphanumeric strings in your worksheet cells.  The user defined function (UDF), I’m presenting here, separates numbers from a cell containing numbers and text characters. The referenced cell can contain any string, including spaces, decimal points or negative numbers.

Function PullNum(rng As Range, _
     Optional Point As Boolean, Optional Negat As Boolean) As Double
'Pulls a number from a cell containing alphanumerics
'e.g. =PullNum(A1,,TRUE) pulls negative number
    Dim cnt As Integer, i As Integer, sLen As Integer
    Dim sTxt As String, sMinus As String, sDP As String, sN As String
    Dim sV As Variant
    sTxt = rng
    If Point = True And Negat = True Then
        sMinus = "-": sDP = "."
    ElseIf Point = True And Negat = False Then
        sMinus = vbNullString: sDP = "."
    ElseIf Point = False And Negat = True Then
        sMinus = "-": sDP = vbNullString
    End If
    sLen = Len(sTxt)
    For cnt = sLen To 1 Step -1
    sV = Mid(sTxt, cnt, 1)
        If IsNumeric(sV) Or sV = sMinus Or sV = sDP Then
            i = i + 1
            sN = Mid(sTxt, cnt, 1) & sN
                If IsNumeric(sN) Then
                    If CDbl(sN) < 0 Then Exit For
                Else
                  sN = Replace(sN, Left(sN, 1), "", , 1)
                End If
        End If
    If i = 1 And sN <> vbNullString Then sN = CDbl(Mid(sN, 1, 1))
    Next cnt
    PullNum = CDbl(sN)
End Function

To use the function, copy its code to any of the Modules in your workbook (add a Module, if not included yet in the workbook). The function can be used in several formats, depending on your optional needs, e.g.:

=pullnum(A1)
=pullnum(A1,TRUE)
=pullnum(A1,TRUE,TRUE)
=pullnum(A1,,TRUE)
=pullnum(A1,FALSE,TRUE)

Here are some examples of output obtained with the function: