Search code examples
excelexcel-formula

Excel formula to extract number and remove unwanted characters

Does anyone know what Excel formula could be used to remove the * and [LETTER] in a list of cells like below, please? The rows update automatically, so the formula needs to apply to numbers with or without the * and [LETTER].

    *23.000*
    16.571
    22.357[d]
    17.125
    12.583
    13.500
    *14.200*
    11.800
    3.500[e]
    6.750
    8.500
    4.900[a]
    5.900

The code below removes the [LETTER] but numbers with an asterisk as the first and last character obviously remain.

=TRIM(LEFT(Sheet1!E4,SEARCH("[",Sheet1!E4&"[")-1))

Solution

  • If you have Microsoft-365 then could try-

    =TAKE(TEXTSPLIT(SUBSTITUTE(E4,"*",""),"["),,1)

    enter image description here