-1

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,))))))))))
7
  • 1
    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.
    – Erik Tyler
    Jul 2 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.
    – Tedinoz
    Jul 3 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 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 at 22:23
  • 1
    yes, that is the correct post thread. I have included below a full explanation of my solution offered in the other post.
    – Erik Tyler
    Jul 12 at 0:28
1

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 SEARCHed 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 SEARCHed 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.