How to extract numbers with separation in Excel

Question

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

Have you considering changing the number format of the cells with the results? — cybernetic.nomad, Oct 26 '22 at 20:07
the number is coming from external font, I would like to change but if I use the formula is going be awesome, quick and easy way for me to build the report. — David Knight, Oct 26 '22 at 20:09
What is the role of `Q3` in our formula, my understanding is that only cell `A1` will be required. — David Leal, Oct 26 '22 at 20:11
Would it always have this format? Or is the content more dynamic? Meaning; does the number always start at position 7? — JvdV, Oct 26 '22 at 20:12
@DavidNunes can you try this and let me know `=MAX(IFERROR(--MID(SUBSTITUTE(A1,"]",""),ROW($ZZ1:INDEX($ZZ:$ZZ,LEN(A1))),LEN(A1)),0))` — Mayukh Bhattacharya, Oct 26 '22 at 20:13
@MayukhBhattacharya this formula below it works perfect and helps me! =MAX(IFERROR(--MID(SUBSTITUTE(A1,"]",""),ROW($ZZ1:INDEX($ZZ:$ZZ,LEN(A1))),LEN(A1)),0)) — David Knight, Oct 26 '22 at 20:20

Mayukh Bhattacharya · Answer 1 · 2022-10-26T20:24:44.720

1

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

edited Oct 26 '22 at 20:24

answered Oct 26 '22 at 20:14

1

@DavidLeal post edit doesn't shows that, it was from the beginning, never mind, I don't post solutions without OP query history. Furthermore, OP has never accepted anyone's solution yet, which surprises more isnt it weird. – Mayukh Bhattacharya Oct 26 '22 at 23:15
Mayukh. No all changes are reflected in the history there is a grace period. I don’t know the duration of this grace period. During this period changes are not reflected in the history. I hope @DavidNunes will solve the mistery, :-). Anyway good talking to you. – David Leal Oct 26 '22 at 23:32

David Leal · Accepted Answer · 2022-10-27T15:40:58.923

1

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

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

Here is the output:

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

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

edited Oct 27 '22 at 15:40

answered Oct 26 '22 at 20:15

David Leal

2 Answers2