How to extract numbers with separation in Excel-CodePudding

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

CodePudding user response：

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

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

Here is the output:

CodePudding user response：

You can use a formula like as below:

• Formula used in cell B1

=MAX(IFERROR(--MID(SUBSTITUTE(A1,"]",""),ROW($ZZ1:INDEX($ZZ:$ZZ,LEN(A1))),LEN(A1)),0))

• With OFFICE 365, you can try this in cell C1

=--INDEX(TEXTSPLIT(A1,{":","[","]"},,1),,2)

• Formula used in cell D1

=SUBSTITUTE(TEXTAFTER(A1," "),"]","")*1