CODEWITHSUNDEEP
excelexcel-formulaformulaexcel-2019
David Knight
David Knight

Reputation: 49

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

Upvotes: 0

Views: 139

Answers (2)

David Leal
David Leal

Reputation: 6759

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: ",""),"]","")

Upvotes: 1

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27263

You can use a formula like as below:

enter image description here

• 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

Upvotes: 1

Related Questions