Does anyone understand FIND and LEN formaulae?

[Carty]

Hi.
Cell C3 contains “Not Specified, Not Specified, NotSpecified” however this is not always the case, it may contain a mixture oftext, symbols and numbers (i.e. there won’t always be 14 characters betweencommas)
Cells C21, C22 and C23 contain a formula to split C3 intothree separate values
C21 has =LEFT($C$3,FIND(",",$C$3)-1) I take thisto mean find a comma in C3 then report whatever is to the left of it
C22 has =MID($C$3,FIND(",",$C$3)+2,FIND(",",$C$3,FIND(",",$C$3)+1)-FIND(",",$C$3)-2).
If I’m honest I have no idea what this is doing - I losemyself in the nested function – but this reports whatever is between the firstand second commas, deleting the space between the comma and the N of Not Specified
C23 has =RIGHT($C$3,LEN($C$3)-FIND(",",$C$3,FIND(",",$C$3)+1))
This reports whatever is to the right of the second comma.There is a problem with this formula in that it includes the space between thecomma and the N of Not Specified. Since cell E23 is looking for “Not Specified”in this cell not “ Not Specified”, cell E23 is not reporting correctly.
Can anyone help me to understand the nested function so thatI can make the formula in C23 give me “Not Specified”?
I appreciate the easy option here is to tell E23 to look for“ Not Specified” but I’d like to understand this a little better
Thanks

 

[steve the fish]

You could just wrap your formula with TRIM. Or you could use the following in cell C21 and drag in down:

=TRIM(MID(SUBSTITUTE($C$3,",",REPT(" ",LEN($C$3))),LEN($C$3)*(ROWS($C$3:C3)-1)+1,LEN($C$3)))

 

[gallen]

I'm no formula guru but I know you could just wrap it in a trim function so it will remove any spaces before and after:

=TRIM(LEFT($C$3,FIND(",",$C$3)-1))

Do that for each one and it will work

Edit: Like a horse's tail - I'm always behind

 

[Carty]

Thanks people. I'll give both options a go

 

[RoryA]

Just for information, the FIND function has an optional third argument which specifies the position to start looking. So with this:

FIND(",",$C$3,FIND(",",$C$3)+1)

the part in red finds the position of the first comma, then we add 1 so that the next FIND operation starts from just after that first comma - hence it finds the second comma. Does that help?

 

[Carty]

Just for information, the FIND function has an optional third argument which specifies the position to start looking. So with this:

FIND(",",$C$3,FIND(",",$C$3)+1)

the part in red finds the position of the first comma, then we add 1 so that the next FIND operation starts from just after that first comma - hence it finds the second comma. Does that help?

So effectively the formula is saying find a comma in C3 start 1 character after finding a second comma in C3?

 

[RoryA]

No, it is saying start looking 1 character after the first comma.

Let's say the cell contains:

This, That, Those

Then the steps for this:

FIND(",",$C$3,FIND(",",$C$3)+1)

are the red part finds the first comma position, which is 5, then we add 1. The formula is then basically:

FIND(",",$C$3,6)

so we find a comma, but starting from position 6. Note though that the result is counted from the start of the whole text, so it returns 11. That's why your MID function's last argument has to then subtract the position of the first comma so that it calculates the length of the text in between the commas.

 

[Carty]

This is where my confusion stats. As far as I can research the syntax for find is this:

FIND(find_text,within_text,[start_num])

find_text is ","
Within_text is $C$3
Start_number is 1 if no nesting involved. That is start 1character after the comma

With nesting

find_text is ","
Within_text is $C$3
start_number is (find "," in c3...... and this is where I lose it. I think that there should be a comma at this point to determine the start number but instead there is a close brackets followed by the +1

 

[RoryA]

Start_num is optional, as you mentioned. In the red section I highlighted, it is left out (so defaults to 1). The + 1 is just so that you don't find the same comma again by starting the second search at the location of the first comma.

Given the same cell containing:

This, That, Those

FIND(",",$C$3,FIND(",",$C$3)+1)

the red part returns 5 since the 5th character is a comma. If you didn't add 1 to that, the blue FIND would start at position 5, which is a comma, so it would return 5 again.

 

[Carty]

Now that makes sense. Thank you for your patience