**Search()**and

**Find()**are pretty much the same. The only difference is that

**Find**worries about matching case (upper/lower) and

**search**doesn’t. That means that Brass is the same as brass as far as

**Search**is concerned, but

**find**thinks they are different. So you can equally apply everything I say about search here to find.

**SEARCH()**is a function that can very quickly turn into something very complex if you are using it, for example, to split up a list a,b,c,d… into separate cells. This is a common thing to want to do, and I don’t understand why there is not a simplified way to do this. It is easy enough in VBA of course, but the majority of Excel users want to do things right there, by means of a built in function.

**SEARCH()**enables this kind of complex string manipulation makes for a good learning exercise.

**What is Search()**

*In Excel, the Search function returns the location of a sub-string in a string. The search is NOT case-sensitive.*

*The syntax for the Search function is:*

Search( text1, text2, start_position )

*text1 is the substring to search for in text2.text2 is the string to search.start_position is the position in text2 where the search will start. The first position is 1.*

*If the Search function does not find a match, it will return a #VALUE! error.*

**An example of where you could use it**

First create a new sheet called Uses, and enter this

We are going to check whether Paul uses Sugar. Very easy since **SEARCH() **will return a number or not.

=IF(ISNUMBER(SEARCH(“Paul”,B2)),”Paul Uses Sugar”,”Paul doesn’t Use Sugar”)

Now C2:E5 look pretty straightforwards – put this formula in C2 and fill over and down – nothing new there**.**

=IF(ISNUMBER(SEARCH(C$1,$B2)),”Yes”,”No”)

**SEARCH()**for that. We are going to count the commas and compare that against the number of times we’ve seen Yes in columns C through E. Counting the commas is actually pretty easy. Use

**SUBSTITUTE()**to get rid of the commas then subtract the number of characters before and after. The answer is the number of commas. I’ve added 1 for the comma that should be at the end of B2. Strictly speaking I should have tested to see if there was one there already, and I should also have tested for B2 being blank – but you get the idea anyway I think.

=IF(LEN(B2)-LEN(SUBSTITUTE(B2,”,”,””))+1- COUNTIF(C2:E2,”Yes”)>0,”Yes”,”No”)

**Using Nested Search()**

**MID()**and

**SEARCH().**It gets complicated really quickly so you have to be careful to put brackets commas etc in the right place.

**Column G**, the first one is easy. I’ve added IFERROR() just to avoid getting the #VALUE error if there is nobody. You can omit that if you want. It was an Excel 2007 addition. I’ve also added a comma to the end of B2. Again I should have tested to see if it was already there etc. as per my previous comments. Essentially this is just taking the 1st part of the string up to the first comma. Put this in G2 and fill down.

=IFERROR(MID(B2,1,SEARCH(“,”,B2 & “,”)-1),””)

**Column H**, the second one is more complicated. In this case we have to take the part of the string after the first comma, but before the second. **SEARCH()** has an optional 3rd argument *Search( text1, text2, start_position ) *** and we are going to need to use that start_position to indicate where the first comma occurred. Put this is H2 and fill down.**

`=IFERROR(MID(B2,SEARCH(",",B2 & ",")+1,SEARCH(",",B2 & ",",SEARCH(",",B2 & ",")+1)-SEARCH(",",B2 & ",")-1),"")`

**Lets break that down. The first occurrence is going to tell us where the 1st character after the first comma is, so MID() can use that to know where the extraction has to start**`SEARCH(",",B2 & ",")+1`

**but what about the length? Well, you have to know where the 2nd comma is for that, so we have to use a nested SEARCH(). That means that we use the position of the 1st comma + 1 as the optional start_position argument to find the 2nd comma. Finally we need to subtract the length of the string that took us up to the 1st comma**`SEARCH(",",B2 & ",",SEARCH(",",B2 & ",")+1)-SEARCH(",",B2 & ",")-1)`

**Column I, Things start to get unreadable. However the principle is very easy and exactly the same as Column H. This time we need to find whats between the 2nd and third comma, so we continue to build up the nesting of SEARCH() to include everything thats gone before. Put this in column I and fill down.**

`=IFERROR(MID(B2,SEARCH(",",B2 & ",",SEARCH(",",B2 & ",")+1)+1,SEARCH(",",B2 & ",",SEARCH(",",B2 & ",",SEARCH(",",B2 & ",",SEARCH(",",B2 & ",")+1)+1)-SEARCH(",",B2 & ",")+1)-1),"")`

**Preparing this data for future use elsewhere**

**create MyUsesBlock, MyUsesHeadings, MyUsesItems, MyUsesWho according to the method in Named Ranges with Column Headings. You should end up with the following definitions**

**MyUsesBlock**

=OFFSET(‘Uses’!$A$1,0,0,COUNTA(‘Uses’!$A:$A),COUNTA(‘Uses’!$1:$1))

**MyUsesHeadings**

=OFFSET(myUsesBlock ,0,0,1)

**MyUsesItems**

=OFFSET(myUsesBlock ,1,MATCH(“Items”,myUsesHeadings,0)-1,rows(myUsesblock)-1,1)

**MyUsesWho**

=OFFSET(myUsesBlock ,1,MATCH(“Who Uses”,myUsesHeadings,0)-1,rows(myUsesblock)-1,1)

#### Sorting chapter/bullets numbers

Quite often you need to sort data that has some kind of chapter numbering, such as

1.1

1.1.2

2.1.1

2.12.2

A specialized form of this would be ip numbers, for example

192.1.3.2

172.12.180.1

These do not lend themselves to sorting easily, so the obvious solution is to insert leading zeroes. The problem though is how to separate the individual components so as to be able to standardize their widths.

One way is to use the search function in Excel. Here is an example of turning such a form into a set of 4 digit numbers so they can be sorted (1.12.11 into 0001.0012.0011.).

=TEXT(IFERROR(MID(B15,1,SEARCH(“.”,B15&”,”)-1),””),REPT(“0″,4)&”.”) & TEXT(IFERROR(MID(B15,SEARCH(“.”,B15 & “.”)+1,SEARCH(“.”,B15 & “.”,SEARCH(“.”,B15 & “.”)+1)-SEARCH(“.”,B15 & “.”)-1),””),REPT(“0″,4)&”.”)& TEXT(IFERROR(MID(B15,SEARCH(“.”,B15 & “.”,SEARCH(“.”,B15 & “.”)+1)+1,SEARCH(“.”,B15 & “.”,SEARCH(“.”,B15 & “.”,SEARCH(“.”,B15 & “.”,SEARCH(“.”,B15 & “.”)+1)+1)-SEARCH(“.”,B15 & “.”)+1)-1),””),REPT(“0″,4)&”.”)

A better way is to use a regEx approach. Although regEx is available in VBA, it is not directly usable as a spreadsheet function. The Excel RegEx library allows you to use a prebuilt library of regexes or to use your own directly in your spreadsheet. Here is the solution to the same problem using regEx. Let’s say the regEx expression ([0-9]+).([0-9]+).([0-9]+) is in A15, and the value to be processed is in B15. The solution is

=TEXT(rxreplace(A15,B15,”$1″),REPT(“0″,4)&”.”) & TEXT(rxreplace(A15,B15,”$2″),REPT(“0″,4)&”.”) & TEXT(rxreplace(A15,B15,”$3″),REPT(“0″,4)&”.”)

**Why use Search()**

**Search**has a number of uses, in particular where the data came from some other source and you didn’t have the chance to organize it. Excel works best when all the

**data elements are in separate cells**. Having multiple information in one cell never works very well.

**Search**can help you split up the data into something more usable.

#### You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my book, Going Gas – from VBA to Apps script