I started over with a fresh idea. I assume all numbers are separated from surrounding text by a space ( ) or a comma space (, ) or a semicolon space (; ). So three substitutions are made to prepare the text string for splitting. We replace all spaces with "|" to create a new text string. Then in the newly created text string, we replace all occurrences of ",|" and ";|" with "|" to clean up the "," and ";" associated with spaces. These steps convert this string:
List: milk 5, bread 3.32, dark choc 4.45; cheese 6.89 eggs 4 new car 20,000.99
to this:
List:|milk|5||bread|3.32|dark|choc|4.45|cheese|6.89|eggs|4|new|car|20,000.99
Then we split the text string at the "|" characters to give this array:
{"List:","milk","5","","bread","3.32","dark","choc","4.45","cheese","6.89","eggs","4","new","car","20,000.99"}
And as before, we try to multiply each of these array elements by 1. If the element looks like a number, it will be converted from text to a true number. And if the array element is interpreted as text only, the multiplication *1 will produce an error, and we replace the error with a blank, leaving this array:
{"","",5,"","",3.32,"","",4.45,"",6.89,"",4,"","",20000.99}
...an array of blanks and numbers. We can sum this to get the final answer (the blanks are ignored).
I like this approach better because it leaves . and , in the text string if those characters are part of the number. And I'm assuming that whether you use a number convention like 1,234.56 or 1.234,56 for the same value, the number should be interpreted correctly according to the local settings.
| MrExcel_20240311.xlsx |
|---|
|
|---|
| A | B |
|---|
| 1 | List: milk 5, bread 3.32, dark choc 4.45; cheese 6.89 eggs 4 new car 20,000.99 | 20024.65 |
|---|
| 2 | List: milk 5, bread 3.32 dark choc 4.45; cheese 6.89 eggs 4 | 23.66 |
|---|
|
|---|
Excel Formula:
=SOM(ALS.FOUT(TEKST.SPLITSEN(SUBSTITUEREN(SUBSTITUEREN(SUBSTITUEREN(A1;" ";"|");",|";"|");";|";"|");"|")*1;""))
