If Function, How do I proceed?

[Jpack2552]

Hi I have a quick question regarding the if function combined with the left function.

My data set looks like this. I only need the left side before the deliminator but with a filter.

88-9999
98-1120
102-1100
102-1200
104-1200
104-1165

I currently have this to separate the first half =LEFT(B1,FIND("-",B1)-1). I have difficulty trying to create a function to filter out certain numbers. For example. I want 102 and 104 but I do do not want- the 11's (102-1100,104-1100) I only want the 12's (104-1200, 102-1200). I only need this for the triple digits but still want to separate the first half.

Is this possible?

 

[MrKowz]

Welcome to the forums!

Based on your example above, what is your desired output? I understand the condition of you don't want the 11's, you only want the 12's. Do you want to return blank if it finds an "11", but return the first 3 numbers when it finds a "12"?

 

[Jpack2552]

Thank you MrKowz.

88-9999 88
98-1120 98
102-1100
102-1200 102
104-1200 104
104-1165

Yes, sorry. I totally forgot about the output. I would like it to return blank if it is an 11 and if it is 12. I would like it to return the first three digits.

Thank you

 

[MrKowz]

This seems to do the trick:

Excel 2013/2016
	A	B
1	88-9999	88
2	98-1120	98
3	102-1100
4	102-1200	102
5	104-1200	104
6	104-1165
Sheet17

Cell Formulas
Range	Formula
B1	=IF(AND(LEN(LEFT(A1,SEARCH("-",A1)-1))=3,MID(A1,SEARCH("-",A1)+1,2)="11"),"",LEFT(A1,SEARCH("-",A1)-1))

 

[Jpack2552]

Thank you so much MrKowz, this is exactly what I was looking for. Can you explain this to me? I want to know how it works.

 

[MrKowz]

Thank you so much MrKowz, this is exactly what I was looking for. Can you explain this to me? I want to know how it works.

I'll see what I can do.

First, we must understand the IF formula. The IF Formula is =IF(condition, value if true, value if false)

In my formula, it can be broken down in the following fashion:
condition = AND(LEN(LEFT(A1,SEARCH("-",A1)-1))=3,MID(A1,SEARCH("-",A1)+1,2)="11")
value if true = ""
value if false = LEFT(A1,SEARCH("-",A1)-1)

Now, to elaborate on the string functions. Lets begin with value if false. This is a common formula to return the left-most values of a string before a specified character. =LEFT(cell, length of string to return) will return the LEFT length of string to return side of the cell. Take a look at the below example for how the LEFT function returns values.

Excel 2013/2016
	A	B
1	String	ABCDEFG
2
3	# of characters to return
4	1	A
5	2	AB
6	3	ABC
7	4	ABCD
8	5	ABCDE
9	6	ABCDEF
10	7	ABCDEFG
Sheet19

Cell Formulas
Range	Formula
B4	=LEFT($B$1,A4)

We use the SEARCH(find text, within text) function to SEARCH for the find text value inside of the within text string. SEARCH returns the character position of the find text value inside of the within text string. Again, see below for an example:

Excel 2013/2016
	A	B
1	String	Position of "-"
2	-23456	1
3	1-3456	2
4	12-456	3
5	123-56	4
6	1234-6	5
7	12345-	6
Sheet19

Cell Formulas
Range	Formula
B2	=SEARCH("-",A2)

We can nest these functions together, but we must subtract 1 from the SEARCH result, since we want everything before the find text value.

Excel 2013/2016
	A	B	C	D
1	String	Position of "-"	Left (including "-")	Left (excluding "-")
2	-23456	1	-
3	1-3456	2	1-	1
4	12-456	3	12-	12
5	123-56	4	123-	123
6	1234-6	5	1234-	1234
7	12345-	6	12345-	12345
Sheet19

Cell Formulas
Range	Formula
B2	=SEARCH("-",A2)
C2	=LEFT(A2,B2)
D2	=LEFT(A2,B2-1)

NOW to tackle the IF condition: AND(LEN(LEFT(A1,SEARCH("-",A1)-1))=3,MID(A1,SEARCH("-",A1)+1,2)="11")

The AND(condition1, condition2, ..., condition n) formula in this case has the following components:
condition 1 = LEN(LEFT(A1,SEARCH("-",A1)-1))=3
condition 2 = MID(A1,SEARCH("-",A1)+1,2)="11"

Lets start with condition 1. Here, we're using the same logic as the LEFT/SEARCH explanation above... we're returning the actual string of everything found before the "-", but in this case, we're adding the LEN(string) function around it to return the LENgth of the string and testing to see if the length of that string is 3.

Excel 2013/2016
	A	B	C	D
1	String	Left (excluding "-")	LEN	Length equals 3?
2	-23456		0	FALSE
3	1-3456	1	1	FALSE
4	12-456	12	2	FALSE
5	123-56	123	3	TRUE
6	1234-6	1234	4	FALSE
7	12345-	12345	5	FALSE
Sheet19

Cell Formulas
Range	Formula
B2	=LEFT(A2,SEARCH("-",A2)-1)
C2	=LEN(B2)
D2	=C2=3

Now, on to condition 2: MID(A1,SEARCH("-",A1)+1,2)="11". The MID function is just another string function. Its syntax is =MID(text, start number, number of characters). Continuing with the examples I've been showing, lets say we want to return the 2 characters to the right of the "-". Note, we must add 1 to the result of SEARCH, because we want the returned value to begin after the found string "-".

Excel 2013/2016
	A	B	C
1	String	Position of "-"	2 characters right of "-"
2	-23456	1	23
3	1-3456	2	34
4	12-456	3	45
5	123-56	4	56
6	1234-6	5	6
7	12345-	6
Sheet19

Cell Formulas
Range	Formula
B2	=SEARCH("-",A2)
C2	=MID(A2,B2+1,2)

In the condition 2, we're just testing to see if the 2 characters found after the "-" are equal to 11. If so, condition 2 returns TRUE.

The AND function will only return TRUE if all conditions inside of it are true. So in this case, if the length of the characters found before the "-" is 3 and the two characters found after the "-" are "11", then we want a TRUE value returned, which will then fire the true condition of the IF statement.

Phew, that was a lot of typing, but I hope it helps to put the entire thing into context!

 

[MrKowz]

JPack - your inbox was full, but I think my response would be helpful to anyone else who happens across this:

To your question of good resources to learn, check out Hiker95's response on this thread: Gratitude

He outlines a TON of resources that you may find useful.

And I appreciate the feedback! One thing that has helped me get to the point where I am now is to always practice, always research, and always try to figure out how something works rather than just accepting the fact that it works. You already have the spark of curiosity, so never let that die out and continue learning. Excel can do some really awesome things once you get into the crazy coding world that is VBA.