Add addition If Condition to existing Formula: Long Formula

[DougStroud]

This task joins a string together based on a number of characters per cell in the range. I want to isolate one range, Col N, and add an IF condition to it.
There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference.

Col. N contains a color term, it is the second color term in the product description and is recognized by the leading slash "/".
N22 = /Lt.Green
This is showing the isolated portion of the formula:
&LEFT(N22,2)&
The current function finds the first two characters in the string, "/L". When the total string is built, the "/" is removed and I am left with "RO408GBLUESWTL151".

In this case: The following string is built from terms in their corresponding cells, which yields me the item name.
"~P ROME BLUE SNB GRL 08 WHT/LT.GREEN 151"
From this, I build the item number:
yields me this
"RO408GBLUESWTL151"
What I desire is this:
"RO408GBLUESWTG151"

What I am hoping to do is add an IF and a FIND Function.
If the leading character is a slash ,"/", and the third character is a period, ".", then use the slash, "/" , find and use the first character after the period, in this case a "G".

Something like If N22 contains a leading slash and a period, then
"/G"

Here is the entire current formula:
Again, isolating the formula in N is the focus. If I need to use a helper column to achieve this goal, that is fine. I am just hung up on how to find the period, "." the next following character and join back w/ the slash, "/".

=IF(LEN(U22)=5,F22&LEFT(P22,2)&LEFT(Q22,1)&LEFT(I22,4)&LEFT(J22,1)&LEFT(K22,1)&LEFT(M22,1)&RIGHT(M22,1)&LEFT(N22,2)&LEFT(R22,2)&LEFT(O22,6)&LEFT(U22,1)&RIGHT(U22,4),
IF(LEN(U22)=4,F22&LEFT(P22,2)&LEFT(Q22,1)&LEFT(I22,4)&LEFT(J22,1)&LEFT(K22,1)&LEFT(M22,1)&RIGHT(M22,1)&LEFT(N22,2)&LEFT(R22,2)&LEFT(O22,6)&LEFT(U22,1)&RIGHT(U22,3),
IF(LEN(U22)=3,F22&LEFT(P22,2)&LEFT(Q22,1)&LEFT(I22,4)&LEFT(J22,1)&LEFT(K22,1)&LEFT(M22,1)&RIGHT(M22,1)&LEFT(N22,2)&LEFT(R22,2)&LEFT(O22,6)&LEFT(U22,1)&RIGHT(U22,2),
IF(LEN(U22)=2,F22&LEFT(P22,2)&LEFT(Q22,1)&LEFT(I22,4)&LEFT(J22,1)&LEFT(K22,1)&LEFT(M22,1)&RIGHT(M22,1)&LEFT(N22,2)&LEFT(R22,2)&LEFT(O22,6)&LEFT(U22,1)&RIGHT(U22,1),
IF(LEN(U22)=1,F22&LEFT(P22,2)&LEFT(Q22,1)&LEFT(I22,4)&LEFT(J22,1)&LEFT(K22,1)&LEFT(M22,1)&RIGHT(M22,1)&LEFT(N22,2)&LEFT(R22,2)&LEFT(O22,6)&U22,
IF(LEN(U22)=0,F22&LEFT(P22,2)&LEFT(Q22,1)&LEFT(I22,4)&LEFT(J22,1)&LEFT(K22,1)&LEFT(M22,1)&RIGHT(M22,1)&LEFT(N22,2)&LEFT(R22,2)&LEFT(O22,6)))))))

 

[NBVC]

Does this work for you?

=REPLACE(Y11,FIND("/",Y11)+1,1,MID(Y11,FIND(".",Y11)+1,1))

 

[DougStroud]

V-
You rock!

 

[DougStroud]

If the cell does not contain the slash and period the formula returns #Value!

I thought by having the leading IF condition that it would catch this and if it did not, the false argument would kick in. Not so.

=IF(SUM(AND(LEN(Y11)-LEN(SUBSTITUTE(Y11,"/","")),LEN(Y11)-LEN(SUBSTITUTE(Y11,".","")))>1),REPLACE(Y11,FIND("/",Y11)+1,1,MID(Y11,FIND(".",Y11)+1,1)),Y11)

 

[DougStroud]

Resolved:
I have not tested w/ multiple slashes or periods to know if the second formula will handle it or whether I need the first one.
But thought it good to post so anyone looking for this in the future would have a reference.

either this
=IF(SUM(AND(LEN(Y11)-LEN(SUBSTITUTE(Y11,"/","")),LEN(Y11)-LEN(SUBSTITUTE(Y11,".","")))>=2),IF(ISERROR(REPLACE(Y11,FIND("/",Y11)+1,1,MID(Y11,FIND(".",Y11)+1,1))),Y11,REPLACE(Y11,FIND("/",Y11)+1,1,MID(Y11,FIND(".",Y11)+1,1))))

or this
=IF(ISERROR(REPLACE(Y11,FIND("/",Y11)+1,1,MID(Y11,FIND(".",Y11)+1,1))),Y11,REPLACE(Y11,FIND("/",Y11)+1,1,MID(Y11,FIND(".",Y11)+1,1))))