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

 

[Norie]

Doug

I'm sure I've probably asked this before but what the heck.

What's the purpose of this?

What will this be used for?

"RO408GBLUESWTG151"

 

[DougStroud]

This is an item number for a product. The item number directly correlates to specific criteria of the product.
Look at the Item Name/description. This value gets reduced down to its important elements in the item record number.

 

[Norie]

Doug

I sort of figured that.

What I'm really trying to work out, and have been for sometime, is the overall goal(s) of what you are doing.

 

[Aladin Akyurek]

Care to post some example strings and the corresponding result strings along with the rules which must hold for input strings?

 

[DougStroud]

Norie,
Thanks.
This is one project w/ two major components.
1) creating record numbers for new inventory items, and correcting the older ones to fit into a standard number convention. This is for approximately 15,000-20,000 items. And all these items have their particulars.
2) The second component is linking this inventory from the point of sale to an online store ecommerce site. The Pos, point of sale, has a large number of limitations. The online store is just the opposite, it has unlimited record length, name length, an extra category level are amongst some of the most notable differences.

What the immediate question is dealing w/ is refining the automation of the item record number creation process.

 

[Norie]

Doug

I'm still not getting the 'big' picture, probably me though.

If you want help with this specific issue I suggest you post some sample data as has been suggested.

 

[DougStroud]

Hello Aladin,
Certainly. If you want me to send the formulas up, please let me know.
I have taken the liberty of bolding and highlighting the desired character that I would need changed.

Item Name/Description:
~P ROME SOLUTION SNB 08 WHT/LT.GREEN 154
Item Record#:
RO48SOLSWL54

Desired Record#:
RO48SOLSWG54

Item Name/Description:
~P ROME MACHINE SNB 08 WHT/LT.BLUE 161
Item Record#:
RO48MASWTL16

Desired Record#:
RO48MASWTB61

Item Name/Description:
~P ROME AGENT SNB 08 RED/DK.BLUE 156
Item Record#:
RO48AGSRDD56

Desired Record#:
RO48AGSRDB56

One final Record that uses a slash, but does not have a character change.
Item Name/Description:
~C ROME MADISON BIND GRL 08 BLK/GLD S/M
Item Record#:
RO48MAGBBGSM

edited for corrections/clarity
Aladin, you had asked me of any particular limitations or rules, the number has to stay intact exactly as it is with the exception of the character swap. Some of the values are directly linked to the immediate preceeding and following item records.

The numbers that are generated must be between 8 & 12 characters.

 

[DougStroud]

I am playing w/ a formula to see if I can swap some letters around:

This is the original value in Y11:
"~P ROME SOLUTION SNB 08 WHT/LT.GREEN 154"

I have constructed this formula to replace the first character following the first slash- but it only results in the exact original term
=IF(AND(FIND("/",Y11,1),FIND(".",Y11,1)),SUBSTITUTE(Y11,FIND(".",Y11,1)-2,"G"),"")
yields:
"~P ROME SOLUTION SNB 08 WHT/LT.GREEN 154"
Was hoping to achieve:
"~P ROME SOLUTION SNB 08 WHT/GT.GREEN 154"

 

[DougStroud]

Here is what I have so far:
Original Value in Y11
~P ROME SOLUTION SNB 08 WHT/LT.GREEN 154

My new formula=:
"The first half of the formula tests for a slash and a period are in the string."

=IF(SUM(AND(LEN(Y11)-LEN(SUBSTITUTE(Y11,"/","")),LEN(Y11)-LEN(SUBSTITUTE(Y11,".","")))>1),SUBSTITUTE(Y11,RIGHT(Y11,LEN(Y11)-FIND("/",Y11)*1),"G",1),Y11)
Gets me to here, which is closer:
~P ROME SOLUTION SNB 08 WHT/G

but I need the rest of the string to the right, as the example below indicates:
~P ROME SOLUTION SNB 08 WHT/GT.GREEN 154

To reiterate, I am looking to swap the first character after the last slash, w/ the first character after the last period after the last slash.
Simply, the "L" gets replaced by the "G"

Any ideas please?

thanks