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
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
