Extract and summarize decimal values

[Ali4772]

Hi everyone
I have a string like ( jhsjdhbk 5.12 sdbasucibac 24.25)
I need to extract both decimal values and summarize
the result should be 5.12 + 24.25 = 29.37

Thanks

 

[Joe4]

Welcome to the Board!

Will it ALWAYS follow this format?
Word <space> number <space> word <space> number

If not, please show examples of ALL the different ways this may be structured.

 

[Ali4772]

Welcome to the Board!

Will it ALWAYS follow this format?
Word <space> number <space> word <space> number

If not, please show examples of ALL the different ways this may be structured.

No every cell is different
it could have 3 value or 4 value
the main requirement is extracting all values separately and then sum them

 

[Joe4]

Hmmm, some very advanced people may be able to come up with an Excel formula that can do it, but the only way I can think of is to create my own User Defined Function in VBA.
Once created, you would simply just use this function like any other native Excel function.
Are you open to that kind of solution?
I don't want to go through the trouble of writing it if you are not willing or able to use VBA (you don't need to know any VBA, what I will create for you is just a "plug-and-play" solution).

 

[Cubist]

Try:

=SUM(IFERROR(--TEXTSPLIT(A1," "),0))

 

[Joe4]

Try:

=SUM(IFERROR(--TEXTSPLIT(A1," "),0))

Oh, I forgot that the SUM function will simply ignore text entries (and it won't cause any errors!), making this much simpler than I thought!
Nice work, Cubist!

 

[Ali4772]

Try:

=SUM(IFERROR(--TEXTSPLIT(A1," "),0))

Great, Thanks

 

[Rick Rothstein]

Another way without using IFERROR...

=SUM(TOROW(0+TEXTSPLIT(A1," "),3))