DougStroud
Well-known Member
- Joined
- Aug 16, 2005
- Messages
- 2,976
- Office Version
- 365
- Platform
- MacOS
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)))))))
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)))))))