TekBytz

Office

How to find a substring between Nth and (N+1)th occurance of a charecter in a cell in Excel

by on Jun.21, 2013, under Office, Windows

Excel’s built-in functions can do some pretty clever stuff, but unfortunately Excel has no single function that will return the  string beween nth and n+1th charecter occurrence of specified data. Fortunately, there are ways to make Excel do this. The function used could be complex but could be accomblished.

Consider if you have the text “How to find the substring in Excel” in cell A1 and incase if you need to find 5th word in the string the below formula can be used. This can be used for strings with any seperators

=IF(ISERR(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),4))+1,(FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),5))-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),4))-1))),"",MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),4))+1,(FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),5))-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),4))-1)))

 As a Generic formula the below paramters can be changed to achive to find any word between occurances

=IF(ISERR(MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),n))+1,(FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),m))-FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),n))-1))),"",MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),n))+1,(FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),m))-FIND(CHAR(1),SUBSTITUTE(A1,"C",CHAR(1),n))-1)))

 Where

A1 – Cell of the string

C – is the Charecter occurance, can be ” “, “.” etc

n – Nth occurance of Charecter C, n starts from 0

m – N+1 occurance of Charecter C, m starts from 0

 Few other useful formulas that would handle strings.

Finding the Nth position of a charecter

=FIND(CHAR(1),SUBSTITUTE(A1,”i”,CHAR(1),3))

This formula will inf the 3rd occurance if charecter “i” in cell A1.

Alternte formula with similar function

=SEARCHB("i",A1,(SEARCHB("i",A1,(SEARCHB("i",A1,1)+1))+1))

Finding the first Word in a String

=IF(ISERR(MID(A1,1,FIND(" ",A1)-1)),"",MID(A1,1,FIND(" ",A1)-1))

This formula will find the 1st word in a sting in cell A1.

Leave a Comment :, more...

Comparing two columns in excel spreedsheet

by on Sep.22, 2008, under Office

If you in a software service based industry you may get many questions in Microsoft Excel. One such question is “How to compare two columns in the excel if the data is in thousands of rows”. Here is a solution for it.

Imagine you have two columns A and B. Column A has the data which needs to be compared against column B. Here I would show you 3 excel formulas to archive this.

  1. MATCH
  2. COUNTIF
  3. VLOOKUP

Here is the syntax

  1. =Match( value, array, match_type )
  2. =CountIf( range, criteria )
  3. =VLookup( value, table_array, index_number, not_exact_match )

Here is a example usage :

  1. The below syntax can be used if we want to find out a match and mark the result as ‘Y’ or ‘N’ and consolidate it.
    =IF(ISNA(MATCH(A1,$B$1:$B$100,FALSE)),"N","Y")
  2. The below syntax is used to find the count of the matches in the resultant column. If the count is 0, then there is no match. If the count is 1 then there is 1 match. If the count is 2 then the matched data count is 2 and so on.
    COUNTIF($B$1:$B$100,A1)
  3. The usage below is similar to the first option where there is change in the function idiom
    IF(ISNA(VLOOKUP(A1,$B$1:$B$100,1,FALSE)),"N","Y")

Where to use what?

Now the question is, of all of them which performs the same function and now where to use what function. The MATCH/VLOOKUP can be used to find out the existence of the data in the column but COUNTIF can be used to find out the existence of data and to find the number of instances the data exists.

Which one is faster?

MATCH/VLOOKUP is more faster than the COUNTIF, since COUNTIF has to look up the entire array to throw out the count. But MATCH/VLOOKUP terminates it loop once it find the data in the array.

There are more functions too in excel for comparison of data. I shall explain it when I come across it.

4 Comments :, , , more...

Looking for something?

Use the form below to search the site:

Still not finding what you're looking for? Drop a comment on a post or contact us so we can take care of it!