#### DougStroud

##### Well-known Member

- Joined
- Aug 16, 2005

- Messages
- 2,968

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