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.
