Search code examples
excelexcel-formulaformulaexcel-2019

How to extract numbers with separation in Excel

Does anyone knows any formula to extract the number with separation (dot, comma) from cell A1 to cell B1?

Example, I want to extract 2,590.00 from cell A1 which has the following value:

[sum: 2,590.00]

I got the formula below that works nice, however is just getting all numbers e.g. 259000

{=TEXTJOIN("",TRUE,IFERROR((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1),""))}

I appreciate every support

Solution

  • Under O365 you can try the following in cell B1 which is a very concise approach:

    =TEXTAFTER(TEXTBEFORE(A1,"]"), "sum: ")

    Here is the output:

    sample excel file

    For , similar idea but using SUBSTITUTE instead to remove the prefix ([sum: ) and the suffix (]):

    =SUBSTITUTE(SUBSTITUTE(A1,"[sum: ",""),"]","")