# Compound Equation

I have a sheet that I use for inventory. We use a letter cost code, that our employee enters when scanning the item. It is as follows. B-1, L-2, A3, C-4, K-5, R-6, O-7, U-8, G-9, E-0

I am trying to make those letters however entered (ie, UUC) display their corresponding number (884)in a separate column. Here is what I wrote, but keep getting back the answer of false.

``````=if(I586="B",1,if(I586="L",2,if(I586="A",3,if(I586="C",4,if(I586="K",5,if(I586="R",6,if(I586="O",7,if(I586="U",8,if(I586="G",9,if(I586="E",0,))))))))))
``````
• Moderators: I feel that this poster's question is substantially different from the one linked to under "This question has already been answered here." The linked post involved substituting one letter with one number value. This poster is asking how to replace a string of numbers with one compound number made of place values (ones, tens, hundreds, etc.). I recommend reopening the post. Jul 2, 2021 at 4:01
• Moderators: This question may not be a duplicate of Assigning a value to a letter in Google Sheets. However, it IS a duplicate of Multiple substitutions in a single text. Jul 3, 2021 at 2:42
• @ErikTyler I reopened the question. P.S. Including "Moderators:" in a comment is not an effective way to get the moderators attention as that doesn't trigger any notification us. In this particular case as I edited the question I can will get a notification if I'm mentioned by name in a comment. Also I review the Web Applications Chat regularly.
– Rubén
Jul 11, 2021 at 22:21
• @Tedinoz As I said in my reply to Erik I just reopened this post. It's opt to you to vote to close as duplicate to the post that you suggests. I'll refrain my self to vote to close again for a couple of days.
– Rubén
Jul 11, 2021 at 22:23
• yes, that is the correct post thread. I have included below a full explanation of my solution offered in the other post. Jul 12, 2021 at 0:28

In the poster's spreadsheet, Column I has a header followed by one of the four following entry types:

• Numbers in currency format (two decimal places)

• The text string "NA" or "N/A" appearing in either form

• A string containing limited letter code as described in the post

• Null

It is my understanding based on context that the poster wants codes to translate into dollars-and-cents currency (e.g., UUC = 884 = \$8.84).

Based on this, I offered the following solution on this post thread:

First, select Column L entirely and delete everything in it.

For the sake of processing speed, it is always best to keep your sheets trimmed to only what you need. So I recommend that you next delete all rows below your data that are unoccupied and likely to remain unoccupied.

Then, place the following formula in L1:

`=ArrayFormula( {"HEADER OF CHOICE"; IF( (ISNUMBER(I2:I)) + (LEFT(I2:I)="N") + (I2:I=""), I2:I, VALUE( SUBSTITUTE( TRANSPOSE( QUERY( TRANSPOSE( ARRAY_CONSTRAIN( IFERROR( RIGHT( SEARCH( SPLIT( REGEXREPLACE( I2:I & REPT("X", 10), "(.)", "\$1~"), "~"), "BLACKROUGE"))), ROWS(I2:I), 10)),, 10)), " ", "")) / 100)} )`

This one formula will create the header (which you can change inside the formula as you like) and all column results, accounting for your different entering of "N/A" (also entered on the sheet as "NA"), blanks, currency and code.

How It Works

The header text can be changed within the formula as desired.

`IF( (ISNUMBER(I2:I)) + (LEFT(I2:I)="N") + (I2:I=""), I2:I, ...`

If the Column-I input is either currency or anything beginning with "N", return the same to Column L. (Note: "N" is not among the code letters, so it will only act on the "NA" or "N/A" text strings.)

Otherwise, working from the inside out...

`REGEXREPLACE( I2:I & REPT("X", 10), "(.)", "\$1~")`

This will append 10 iterations of "X" to whatever is in I2:I (which, for all intents and purposes, will only be the codes at this point) and then replace the entire new string with each original character followed by a tilde, e.g., `UUC` will become `U~U~C~X~X~X~X~X~X~X~X~X~X~`. The appended "X" characters are not code letter, so these will allow each string to be truncated to an even 10 characters later on regardless of its original length; such uniformity will be necessary at a later stage in the formula.

`SPLIT( REGEXREPLACE( I2:I & REPT("X", 10), "(.)", "\$1~"), "~")`

The above strings will be `SPLIT` to separate columns at the tildes.

`SEARCH( SPLIT( REGEXREPLACE( I2:I & REPT("X", 10), "(.)", "\$1~"), "~"), "BLACKROUGE")`

Each of these characters will then be `SEARCH`ed within a composite of the code letters in order of value from 1 to 10, i.e., `BLACKROUGE` with `B` being worth 1 and `E` being worth 10.

`RIGHT( SEARCH( SPLIT( REGEXREPLACE( I2:I & REPT("X", 10), "(.)", "\$1~"), "~"), "BLACKROUGE"))`

If not for the inclusion of the two-digit 10, this step could have been eliminated. However, since we do have the 10 (which is worth 0 in the code), we will simply take the `RIGHT` single character from each `SEARCH`ed location. This will return a value of 0-9 for the code letters (or an error for the 'X' characters, which are not found within 'BLACKROUGE').

`IFERROR( RIGHT( SEARCH( SPLIT( REGEXREPLACE( I2:I & REPT("X", 10), "(.)", "\$1~"), "~"), "BLACKROUGE")))`

`IFERROR` will control for the above errors where "X" occurs, leaving those null.

`ARRAY_CONSTRAIN( IFERROR( RIGHT( SEARCH( SPLIT( REGEXREPLACE( I2:I & REPT("X", 10), "(.)", "\$1~"), "~"), "BLACKROUGE"))), ROWS(I2:I), 10)`

Here, we see the reason for the extra 'X' characters, as `ARRAY_CONSTRAIN` can now extract a uniform array of `I2:I` rows and 10 columns. Each of those 10 columns of data will now either be 0-9 or null.

`TRANSPOSE( QUERY( TRANSPOSE( ARRAY_CONSTRAIN( IFERROR( RIGHT( SEARCH( SPLIT( REGEXREPLACE( I2:I & REPT("X", 10), "(.)", "\$1~"), "~"), "BLACKROUGE"))), ROWS(I2:I), 10)),, 10))`

This is a standard "trick" for exploiting a feature of the `QUERY` function. That is, one need not only select `0` or `1` as the number of headers to show for the `QUERY`. This combination of functions, flips the array created by `ARRAY-CONTRAIN`, asks `QUERY` to return no table data but 10 headers (which will be all of the column data we created above of 1-9 and nulls), then flip it back to its original orientation. The end result is that all of the 0-9 and null characters will form one string per row with spaces between each original character.

`SUBSTITUTE( TRANSPOSE( QUERY( TRANSPOSE( ARRAY_CONSTRAIN( IFERROR( RIGHT( SEARCH( SPLIT( REGEXREPLACE( I2:I & REPT("X", 10), "(.)", "\$1~"), "~"), "BLACKROUGE"))), ROWS(I2:I), 10)),, 10)), " ", "")`

`SUBSTITUTE` removes those spaces, forming one contiguous string of characters per row.

`VALUE( SUBSTITUTE( TRANSPOSE( QUERY( TRANSPOSE( ARRAY_CONSTRAIN( IFERROR( RIGHT( SEARCH( SPLIT( REGEXREPLACE( I2:I & REPT("X", 10), "(.)", "\$1~"), "~"), "BLACKROUGE"))), ROWS(I2:I), 10)),, 10)), " ", ""))`

`VALUE` will convert those strings of numbers into actual numeric values.

`VALUE( SUBSTITUTE( TRANSPOSE( QUERY( TRANSPOSE( ARRAY_CONSTRAIN( IFERROR( RIGHT( SEARCH( SPLIT( REGEXREPLACE( I2:I & REPT("X", 10), "(.)", "\$1~"), "~"), "BLACKROUGE"))), ROWS(I2:I), 10)),, 10)), " ", "")) / 100`

The addition of `/ 100` will convert those numeric values to dollars-and-cents.