I need to compare values in a spreadsheet and if it matches print a value in another cell

Question

I have data in Excel, which is always going to be a fixed length (17 alpha-numeric characters.) Each character should correlate to something like

Requirement: All alphabet characters are represented by the letter "A" All numeric characters are represented as a "9" If a given field can be either alpha, or numeric, an "X" should be the wild card

Compare D1 to E1. If the value from E1 is in the alpha_range (I used a named range) cell F1 should be updated to reflect "A". If the value in E1 is in the numeric range, cell F1 should be updated to reflect "9"

If the above E1 does not match either "alpha_range" or "num_range", then F1 is updated to reflect "X"

I have currently used a formula to split the alpha-numeric value into columns by position (LEFT, MID, RIGHT)

I did the same for the 'template' data

I just can't quite figure out how to compare each cell to determine whether the value fits in one range, or another (and if not, then output X into a different cell)

Also, I need to be able to have the values update if I replace the values in the first column (so I can compare repeatedly, without having to manually do these Help!

Answer 1

Maybe try to do this in a single go?

Formula in B4:

=LET(s,SEQUENCE(,13),r,IF(MID(A4,SEQUENCE(,13),1)<"A","9","A"),IF(r=MID(A2,s,1),r,"X"))

Answer 2

This formula compares the values in cells A1 and B1. If they match, it prints the value in cell C1. If they don’t match, it prints an empty string. You can copy this formula to other cells in column D to check for matches in other rows.

=IF(A1=B1,C1,"")

Answer 3

Match Chars in String

=LET(temp,A2,num,A4,nchar,"9",achar,"A",xchar,"X",
    s,SEQUENCE(,LEN(temp)),
    ts,MID(temp,s,1),
    ns,MID(num,s,1),
    nc,CODE(ns),
    as,HSTACK(SEQUENCE(,26,65),SEQUENCE(,26,97)),
    rs,IF(ISNUMBER(--ns),IF(ts=nchar,nchar,xchar),
        IF(ISNUMBER(XMATCH(nc,as)),IF(ts=achar,achar,xchar),xchar)),
    result,CONCAT(rs),
    result)