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.
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”)
=IF(LEN(B2)-LEN(SUBSTITUTE(B2,”,”,””))+1- COUNTIF(C2:E2,”Yes”)>0,”Yes”,”No”)
=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),"")
SEARCH(",",B2 & ",")+1
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),"")
=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)
Quite often you need to sort data that has some kind of chapter numbering, such as
=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)&”.”)
In addition to being simpler to start with, complexity increase is linear as more sets of numbers are added.
Why use Search()
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines