VideoPhoto

Showing posts with label substring. Show all posts
Showing posts with label substring. Show all posts

07 January, 2022

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)

 

06 May, 2021

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)