Forum Discussion

bofus's avatar
bofus
Copper Contributor
May 12, 2023

Remove Letters

Looking for a formula to remove the letters from this string (leaving the X). Number of digits and spaces will vary.

CYL TM DUMP 10.5 X 4.5 X 60SPC                                                 
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    May 13, 2023

    bofus 

    Exactly as previous, just more straightforward

    =REDUCE(
    "",
    MID(A1, SEQUENCE(LEN(A1)), 1),
    LAMBDA(a,v, a & IF(OR(v = {"0","1","2","3","4","5","6","7","8","9",".","X"}), v, ""))
)
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    May 12, 2023

    bofus 

    A 365 solution:

    =LET(
    n, LEN(A1),
    arr, MID(A1, SEQUENCE(n), 1),
    REDUCE("", arr, LAMBDA(a, v, a & IF(OR(v = "x", v = " ", v = ".", ISNUMBER(v * 1)), v, "")))
)
  • mtarler's avatar
    mtarler
    Silver Contributor
    May 12, 2023

    bofus so we have done a few of these get numbers from text and each seems to have different "rules".  Here is a recent one that needed numbers next to a 'tag' value and in their case was also "X":

    =LET(in,A14, tag, "x",
notvals,IFERROR(TEXTSPLIT(in,HSTACK({",","."," "},tag,tag&" "), CHAR(SEQUENCE(10, , 48)), 1),CHAR(1)),
tagVals,TEXTSPLIT(in, notvals,,1),
vals, SUBSTITUTE(FILTER(tagVals,ISERROR(--tagVals),""),tag,""),
out,IFERROR(INDEX(FILTER(vals,NOT(ISERROR(--vals)),0),1),0),
out)

    that said in your case if you just want numbers and Xs to return then you can probably shorten it at line 3 to return that value (tagVals).  So  you can "play" with the above to make it fit your case here is some explanation:

    line 1 defines the input cell and what to use as a "tag" character

    line 2 removes all the characters you WANT in the end so you have a matrix of remaining parts you do NOT want.  The 'things' you want are included in the HSTACK as comma, period, space, the tag character, the tag character followed by a space, and the the CHAR() is all the digits 0-9.

    line 3 uses the result of line 2 to go back to the original input and remove all those things you don't want

    line 4 in this case will filter for ONLY the items found from line 3 that are not just a numeric value (i.e. it has the 'tag' value in the string result) since this version only wanted numbers next to the letter 'X' but then removed that tag character

    line 5 then returned only those remaining items that were numeric after that tag was removed and return the 1st result

    line 6 returned the result from line 5 (out) but you can change that to tagVals to see the result from line 3, which i think might be what you want or close to it.

Resources