How to SUM bold numbers in one cell?

[penkuk]

Hello!

I was trying to find post for this one, but couldnt.
So i got numbers in one cell (B3) 4.3 ± 0.4 and in the next cell (B4)18.5 ± 0.5 and so on ...
Is it possible to sum them? with some code to MVB ? or somehow else ?

something like this, and in the blank space i want to sum them

 

[Sufiyan97]

Hi & Welcome to MrExcel.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Do you want something like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3		4.3 ± 0.4	12.2 ± 0.4	23.1 ± 0.7	27.7 ± 0.8		4.7	12.6	23.8	28.5
4
5
6
7
8
Sheet1

Cell Formulas
Range	Formula
G3:J3	G3	=SUM(--TEXTSPLIT(B3,,CHAR(10)&"±"&CHAR(10)))

 

[penkuk]

Hi & Welcome to MrExcel.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Do you want something like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3		4.3 ± 0.4	12.2 ± 0.4	23.1 ± 0.7	27.7 ± 0.8		4.7	12.6	23.8	28.5
4
5
6
7
8
Sheet1

Cell Formulas
Range	Formula
G3:J3	G3	=SUM(--TEXTSPLIT(B3,,CHAR(10)&"±"&CHAR(10)))

I just want to sum the bold numbers.
Like the first row equals to 14,9

 

[Fluff]

Hi & welcome to MrExcel.
Are the bold numbers always on top? If so how about

Fluff.xlsm
	A	B	C	D	E
1
2	4.3 ± 0.4	12.2 ± 0.4	23.1 ± 0.7	27.7 ± 0.8	67.3
3	4.5 ± 0.4	18.5 ± 0.4	23.4 ± 0.7	28.5 ± 0.8	74.9
Sheet6

Cell Formulas
Range	Formula
E2:E3	E2	=SUM(--TEXTSPLIT(A2:D2,,CHAR(10)))

 

[penkuk]

Hi & welcome to MrExcel.
Are the bold numbers always on top? If so how about

Fluff.xlsm
	A	B	C	D	E
1
2	4.3 ± 0.4	12.2 ± 0.4	23.1 ± 0.7	27.7 ± 0.8	67.3
3	4.5 ± 0.4	18.5 ± 0.4	23.4 ± 0.7	28.5 ± 0.8	74.9
Sheet6

Cell Formulas
Range	Formula
E2:E3	E2	=SUM(--TEXTSPLIT(A2:D2,,CHAR(10)))

i want like this, your formula didnt work, do i need to download something to work? It shows NAME?
if its possible i need to do average on them .
(the red is my sum)

 

[Fluff]

What version of Excel are you using, your profile shows 365 which has the textsplit function?

 

[penkuk]

What version of Excel are you using, your profile shows 365 which has the textsplit function?

Oh , 2016

 

[Fluff]

Ok, try

Fluff.xlsm
	A	B	C	D
1	8.8	30.7	46.5	56.2
2	4.3 ± 0.4	12.2 ± 0.4	23.1 ± 0.7	27.7 ± 0.8
3	4.5 ± 0.4	18.5 ± 0.4	23.4 ± 0.7	28.5 ± 0.8
Sheet6

Cell Formulas
Range	Formula
A1:D1	A1	=SUMPRODUCT(--(LEFT(A2:A3,FIND(CHAR(10),A2:A3)-1)))

 

[penkuk]

Ok, try

Fluff.xlsm
	A	B	C	D
1	8.8	30.7	46.5	56.2
2	4.3 ± 0.4	12.2 ± 0.4	23.1 ± 0.7	27.7 ± 0.8
3	4.5 ± 0.4	18.5 ± 0.4	23.4 ± 0.7	28.5 ± 0.8
Sheet6

Cell Formulas
Range	Formula
A1:D1	A1	=SUMPRODUCT(--(LEFT(A2:A3,FIND(CHAR(10),A2:A3)-1)))

It doesn't work for me, maybe because i got merged cells ?

 

[bebo021999]

VBA option:
Right click on tab name, view code insert module then paste below code into:

Option Explicit
Function sumBold(ByVal rng As Range) As Double
Dim cell As Range, sp
For Each cell In rng
    sp = Split(cell, "+-")
    If cell.Characters(1, Len(sp(0))).Font.Bold Then
        sumBold = sumBold + sp(0)
    End If
Next
End Function

Book1
	B	C	D
1	Use UDF		Test (Use SUM)
2	55.9		55.9
3	14.9+-0.5		14.9
4	4.1 +-0.5		4.1
5	2.8 +- 0.5		2.8
6	12.3+-0.4		12.3
7	14.9 +- 0.5		14.9
8	4.1 +- 0.5		4.1
9	2.8 +- 0.5		2.8
10	12.3 +- 0.4
Sheet1

Cell Formulas
Range	Formula
B2	B2	=sumBold(B3:B10)
D2	D2	=SUM(D3:D10)