How to ignore text on SUM() function...
 

Hi. I'm using the SUM() function to add cell data which contains
bandwidth or kilobyte information. The SUM cell shows the data in
kilobytes. The formula I'm using in the SUM cell is =SUM(CELL RANGE)
& " KB". My problem is that the formula ignores a cell entirely if
someone puts an alpha character after the numerics - ie 1024K instead
of 1024. Is there a way to use the SUM() function and ignore aplha
characters but not numerics? This way if someone types 1024K or 1024
it still will show as 1024K in the sum field.

How to ignore text on SUM() function...
 

try:

=SUM(IF($D$1:$D$5<"",IF(ISNUMBER(--RIGHT($D$1:$D$5)),$D$1:$D$5,--LEFT($D$1:$D$5,LEN($D$1:$D$5)-1))))

Enter with Ctrl+Shift+Enter

"KLZA" wrote:

Hi. I'm using the SUM() function to add cell data which contains
bandwidth or kilobyte information. The SUM cell shows the data in
kilobytes. The formula I'm using in the SUM cell is =SUM(CELL RANGE)
& " KB". My problem is that the formula ignores a cell entirely if
someone puts an alpha character after the numerics - ie 1024K instead
of 1024. Is there a way to use the SUM() function and ignore aplha
characters but not numerics? This way if someone types 1024K or 1024
it still will show as 1024K in the sum field.

How to ignore text on SUM() function...
 

On Aug 1, 4:24 pm, Toppers wrote:
try:

=SUM(IF($D$1:$D$5<"",IF(ISNUMBER(--RIGHT($D$1:$D$5)),$D$1:$D$5,--LEFT($D$1*:$D$5,LEN($D$1:$D$5)-1))))

Enter with Ctrl+Shift+Enter



"KLZA" wrote:
Hi. I'm using the SUM() function to add cell data which contains
bandwidth or kilobyte information. The SUM cell shows the data in
kilobytes. The formula I'm using in the SUM cell is =SUM(CELL RANGE)
& " KB". My problem is that the formula ignores a cell entirely if
someone puts an alpha character after the numerics - ie 1024K instead
of 1024. Is there a way to use the SUM() function and ignore aplha
characters but not numerics? This way if someone types 1024K or 1024
it still will show as 1024K in the sum field.- Hide quoted text -

- Show quoted text -

Not working. I get #VALUE....

How to ignore text on SUM() function...
 

Worked OK with me with following data:

1024K
1024
789K
1234

Sum=4071




"KLZA" wrote:

On Aug 1, 4:24 pm, Toppers wrote:
try:

=SUM(IF($D$1:$D$5<"",IF(ISNUMBER(--RIGHT($D$1:$D$5)),$D$1:$D$5,--LEFT($D$1-:$D$5,LEN($D$1:$D$5)-1))))

Enter with Ctrl+Shift+Enter



"KLZA" wrote:
Hi. I'm using the SUM() function to add cell data which contains
bandwidth or kilobyte information. The SUM cell shows the data in
kilobytes. The formula I'm using in the SUM cell is =SUM(CELL RANGE)
& " KB". My problem is that the formula ignores a cell entirely if
someone puts an alpha character after the numerics - ie 1024K instead
of 1024. Is there a way to use the SUM() function and ignore aplha
characters but not numerics? This way if someone types 1024K or 1024
it still will show as 1024K in the sum field.- Hide quoted text -

- Show quoted text -

Not working. I get #VALUE....

How to ignore text on SUM() function...
 

Worked ok for me, too.

Enter with Ctrl+Shift+Enter
Not working. I get #VALUE....

You'll get #VALUE! if don't array enter**.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Toppers" wrote in message
...
Worked OK with me with following data:

1024K
1024
789K
1234

Sum=4071




"KLZA" wrote:

On Aug 1, 4:24 pm, Toppers wrote:
try:

=SUM(IF($D$1:$D$5<"",IF(ISNUMBER(--RIGHT($D$1:$D$5)),$D$1:$D$5,--LEFT($D$1-:$D$5,LEN($D$1:$D$5)-1))))

Enter with Ctrl+Shift+Enter



"KLZA" wrote:
Hi. I'm using the SUM() function to add cell data which contains
bandwidth or kilobyte information. The SUM cell shows the data in
kilobytes. The formula I'm using in the SUM cell is =SUM(CELL RANGE)
& " KB". My problem is that the formula ignores a cell entirely if
someone puts an alpha character after the numerics - ie 1024K instead
of 1024. Is there a way to use the SUM() function and ignore aplha
characters but not numerics? This way if someone types 1024K or 1024
it still will show as 1024K in the sum field.- Hide quoted text -

- Show quoted text -

Not working. I get #VALUE....

How to ignore text on SUM() function...
 

Here's another one that's a few keystrokes shorter and is normally entered.
Assumes all entries will either be a number or a number followed by the
letter K (case doesn't matter):

1024k
1024K
256
256K

=SUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE(UPPER(A1:A5)&"K","KK","")," K",".0")))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Worked ok for me, too.

Enter with Ctrl+Shift+Enter
Not working. I get #VALUE....

You'll get #VALUE! if don't array enter**.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Toppers" wrote in message
...
Worked OK with me with following data:

1024K
1024
789K
1234

Sum=4071




"KLZA" wrote:

On Aug 1, 4:24 pm, Toppers wrote:
try:

=SUM(IF($D$1:$D$5<"",IF(ISNUMBER(--RIGHT($D$1:$D$5)),$D$1:$D$5,--LEFT($D$1-:$D$5,LEN($D$1:$D$5)-1))))

Enter with Ctrl+Shift+Enter



"KLZA" wrote:
Hi. I'm using the SUM() function to add cell data which contains
bandwidth or kilobyte information. The SUM cell shows the data in
kilobytes. The formula I'm using in the SUM cell is =SUM(CELL
RANGE)
& " KB". My problem is that the formula ignores a cell entirely if
someone puts an alpha character after the numerics - ie 1024K
instead
of 1024. Is there a way to use the SUM() function and ignore aplha
characters but not numerics? This way if someone types 1024K or
1024
it still will show as 1024K in the sum field.- Hide quoted text -

- Show quoted text -

Not working. I get #VALUE....

How to ignore text on SUM() function...
 

=SUMPRODUCT(--(LEFT(A1:A4,FIND("K",UPPER(A1:A4)&"K")-1)&".0"))

Another one that is still a few keystrokes shorter. Pretty sure I got this
idea from one of your previous posts <g

For the OP, you might also consider using data validation to help prevent
folks from entering text. Click Data/Validation and use whole number or
decimal. This formula will only work for entries with a "K" included.


"T. Valko" wrote:

Here's another one that's a few keystrokes shorter and is normally entered.
Assumes all entries will either be a number or a number followed by the
letter K (case doesn't matter):

1024k
1024K
256
256K

=SUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE(UPPER(A1:A5)&"K","KK","")," K",".0")))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Worked ok for me, too.

Enter with Ctrl+Shift+Enter
Not working. I get #VALUE....

You'll get #VALUE! if don't array enter**.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Toppers" wrote in message
...
Worked OK with me with following data:

1024K
1024
789K
1234

Sum=4071




"KLZA" wrote:

On Aug 1, 4:24 pm, Toppers wrote:
try:

=SUM(IF($D$1:$D$5<"",IF(ISNUMBER(--RIGHT($D$1:$D$5)),$D$1:$D$5,--LEFT($D$1-:$D$5,LEN($D$1:$D$5)-1))))

Enter with Ctrl+Shift+Enter



"KLZA" wrote:
Hi. I'm using the SUM() function to add cell data which contains
bandwidth or kilobyte information. The SUM cell shows the data in
kilobytes. The formula I'm using in the SUM cell is =SUM(CELL
RANGE)
& " KB". My problem is that the formula ignores a cell entirely if
someone puts an alpha character after the numerics - ie 1024K
instead
of 1024. Is there a way to use the SUM() function and ignore aplha
characters but not numerics? This way if someone types 1024K or
1024
it still will show as 1024K in the sum field.- Hide quoted text -

- Show quoted text -

Not working. I get #VALUE....

How to ignore text on SUM() function...
 

not sure about the eg data where kb(s) are generated on same format?...

maybe a more versatile formula which will read/release any alpha(s)..can be
interesting?...

--
regards,
driller

*****
- dive with Jonathan Seagull



"JMB" wrote:

=SUMPRODUCT(--(LEFT(A1:A4,FIND("K",UPPER(A1:A4)&"K")-1)&".0"))

Another one that is still a few keystrokes shorter. Pretty sure I got this
idea from one of your previous posts <g

For the OP, you might also consider using data validation to help prevent
folks from entering text. Click Data/Validation and use whole number or
decimal. This formula will only work for entries with a "K" included.


"T. Valko" wrote:

Here's another one that's a few keystrokes shorter and is normally entered.
Assumes all entries will either be a number or a number followed by the
letter K (case doesn't matter):

1024k
1024K
256
256K

=SUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE(UPPER(A1:A5)&"K","KK","")," K",".0")))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Worked ok for me, too.

Enter with Ctrl+Shift+Enter
Not working. I get #VALUE....

You'll get #VALUE! if don't array enter**.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Toppers" wrote in message
...
Worked OK with me with following data:

1024K
1024
789K
1234

Sum=4071




"KLZA" wrote:

On Aug 1, 4:24 pm, Toppers wrote:
try:

=SUM(IF($D$1:$D$5<"",IF(ISNUMBER(--RIGHT($D$1:$D$5)),$D$1:$D$5,--LEFT($D$1-:$D$5,LEN($D$1:$D$5)-1))))

Enter with Ctrl+Shift+Enter



"KLZA" wrote:
Hi. I'm using the SUM() function to add cell data which contains
bandwidth or kilobyte information. The SUM cell shows the data in
kilobytes. The formula I'm using in the SUM cell is =SUM(CELL
RANGE)
& " KB". My problem is that the formula ignores a cell entirely if
someone puts an alpha character after the numerics - ie 1024K
instead
of 1024. Is there a way to use the SUM() function and ignore aplha
characters but not numerics? This way if someone types 1024K or
1024
it still will show as 1024K in the sum field.- Hide quoted text -

- Show quoted text -

Not working. I get #VALUE....

How to ignore text on SUM() function...
 

Yep, nice one!

I had a feeling it could be reduced even further.

You came up with the *best* solution:

consider using data validation to help prevent
folks from entering text.

--
Biff
Microsoft Excel MVP


"JMB" wrote in message
...
=SUMPRODUCT(--(LEFT(A1:A4,FIND("K",UPPER(A1:A4)&"K")-1)&".0"))

Another one that is still a few keystrokes shorter. Pretty sure I got
this
idea from one of your previous posts <g

For the OP, you might also consider using data validation to help prevent
folks from entering text. Click Data/Validation and use whole number or
decimal. This formula will only work for entries with a "K" included.


"T. Valko" wrote:

Here's another one that's a few keystrokes shorter and is normally
entered.
Assumes all entries will either be a number or a number followed by the
letter K (case doesn't matter):

1024k
1024K
256
256K

=SUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE(UPPER(A1:A5)&"K","KK","")," K",".0")))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Worked ok for me, too.

Enter with Ctrl+Shift+Enter
Not working. I get #VALUE....

You'll get #VALUE! if don't array enter**.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Toppers" wrote in message
...
Worked OK with me with following data:

1024K
1024
789K
1234

Sum=4071




"KLZA" wrote:

On Aug 1, 4:24 pm, Toppers wrote:
try:

=SUM(IF($D$1:$D$5<"",IF(ISNUMBER(--RIGHT($D$1:$D$5)),$D$1:$D$5,--LEFT($D$1-:$D$5,LEN($D$1:$D$5)-1))))

Enter with Ctrl+Shift+Enter



"KLZA" wrote:
Hi. I'm using the SUM() function to add cell data which contains
bandwidth or kilobyte information. The SUM cell shows the data
in
kilobytes. The formula I'm using in the SUM cell is =SUM(CELL
RANGE)
& " KB". My problem is that the formula ignores a cell entirely
if
someone puts an alpha character after the numerics - ie 1024K
instead
of 1024. Is there a way to use the SUM() function and ignore
aplha
characters but not numerics? This way if someone types 1024K or
1024
it still will show as 1024K in the sum field.- Hide quoted text -

- Show quoted text -

Not working. I get #VALUE....

How to ignore text on SUM() function...
 

I think if there are any more conditions/variables then the OP should take
JMB's advice:

consider using data validation to help prevent
folks from entering text.

--
Biff
Microsoft Excel MVP


"driller" wrote in message
...
not sure about the eg data where kb(s) are generated on same format?...

maybe a more versatile formula which will read/release any alpha(s)..can
be
interesting?...

--
regards,
driller

*****
- dive with Jonathan Seagull



"JMB" wrote:

=SUMPRODUCT(--(LEFT(A1:A4,FIND("K",UPPER(A1:A4)&"K")-1)&".0"))

Another one that is still a few keystrokes shorter. Pretty sure I got
this
idea from one of your previous posts <g

For the OP, you might also consider using data validation to help prevent
folks from entering text. Click Data/Validation and use whole number or
decimal. This formula will only work for entries with a "K" included.


"T. Valko" wrote:

Here's another one that's a few keystrokes shorter and is normally
entered.
Assumes all entries will either be a number or a number followed by the
letter K (case doesn't matter):

1024k
1024K
256
256K

=SUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE(UPPER(A1:A5)&"K","KK","")," K",".0")))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Worked ok for me, too.

Enter with Ctrl+Shift+Enter
Not working. I get #VALUE....

You'll get #VALUE! if don't array enter**.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Toppers" wrote in message
...
Worked OK with me with following data:

1024K
1024
789K
1234

Sum=4071




"KLZA" wrote:

On Aug 1, 4:24 pm, Toppers
wrote:
try:

=SUM(IF($D$1:$D$5<"",IF(ISNUMBER(--RIGHT($D$1:$D$5)),$D$1:$D$5,--LEFT($D$1-:$D$5,LEN($D$1:$D$5)-1))))

Enter with Ctrl+Shift+Enter



"KLZA" wrote:
Hi. I'm using the SUM() function to add cell data which
contains
bandwidth or kilobyte information. The SUM cell shows the data
in
kilobytes. The formula I'm using in the SUM cell is =SUM(CELL
RANGE)
& " KB". My problem is that the formula ignores a cell
entirely if
someone puts an alpha character after the numerics - ie 1024K
instead
of 1024. Is there a way to use the SUM() function and ignore
aplha
characters but not numerics? This way if someone types 1024K
or
1024
it still will show as 1024K in the sum field.- Hide quoted
text -

- Show quoted text -

Not working. I get #VALUE....

How to ignore text on SUM() function...
 

"T. Valko" wrote...
....
I had a feeling it could be reduced even further.
....

and further still

"JMB" wrote in message
=SUMPRODUCT(--(LEFT(A1:A4,FIND("K",UPPER(A1:A4)&"K")-1)&".0"))
....

=SUMPRODUCT(--(LEFT(A1:A4,SEARCH("K",A1:A4&"K")-1)&".0"))

How to ignore text on SUM() function...
 

On Aug 2, 12:16 am, JMB wrote:
=SUMPRODUCT(--(LEFT(A1:A4,FIND("K",UPPER(A1:A4)&"K")-1)&".0"))

Another one that is still a few keystrokes shorter. Pretty sure I got this
idea from one of your previous posts <g

For the OP, you might also consider using data validation to help prevent
folks from entering text. Click Data/Validation and use whole number or
decimal. This formula will only work for entries with a "K" included.



"T. Valko" wrote:
Here's another one that's a few keystrokes shorter and is normally entered.
Assumes all entries will either be a number or a number followed by the
letter K (case doesn't matter):

1024k
1024K
256
256K

=SUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE(UPPER(A1:A5)&"K","KK","")," K",".0")))

--
Biff
Microsoft Excel MVP

"T. Valko" wrote in message
...
Worked ok for me, too.

Enter with Ctrl+Shift+Enter
Not working. I get #VALUE....

You'll get #VALUE! if don't array enter**.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP

"Toppers" wrote in message
...
Worked OK with me with following data:

1024K
1024
789K
1234

Sum=4071

"KLZA" wrote:

On Aug 1, 4:24 pm, Toppers wrote:
try:

=SUM(IF($D$1:$D$5<"",IF(ISNUMBER(--RIGHT($D$1:$D$5)),$D$1:$D$5,--LEFT($D$1*-:$D$5,LEN($D$1:$D$5)-1))))

Enter with Ctrl+Shift+Enter

"KLZA" wrote:
Hi. I'm using the SUM() function to add cell data which contains
bandwidth or kilobyte information. The SUM cell shows the data in
kilobytes. The formula I'm using in the SUM cell is =SUM(CELL
RANGE)
& " KB". My problem is that the formula ignores a cell entirely if
someone puts an alpha character after the numerics - ie 1024K
instead
of 1024. Is there a way to use the SUM() function and ignore aplha
characters but not numerics? This way if someone types 1024K or
1024
it still will show as 1024K in the sum field.- Hide quoted text -

- Show quoted text -

Not working. I get #VALUE....- Hide quoted text -

- Show quoted text -

Thanks all!!

How to ignore text on SUM() function...
 

Probably could be done w/a helper column, but I don't see how to get it all
in one formula w/o ending up w/an array of arrays (for lack of a better
description). But, just because I don't see it doesn't mean it can't be
done.

I would probably resort to a macro before putting the time into finding a
single formula solution (or smack the person who put the alpha data in and
make them redo it).

Sub Test()
Dim lngCount As Long

For lngCount = 65 To 90
Selection.Replace _
what:=Chr(lngCount), _
replacement:="", _
lookat:=xlPart, _
searchorder:=xlByRows, _
matchbyte:=False
Next lngCount
End Sub




"T. Valko" wrote:

I think if there are any more conditions/variables then the OP should take
JMB's advice:

consider using data validation to help prevent
folks from entering text.

--
Biff
Microsoft Excel MVP


"driller" wrote in message
...
not sure about the eg data where kb(s) are generated on same format?...

maybe a more versatile formula which will read/release any alpha(s)..can
be
interesting?...

--
regards,
driller

*****
- dive with Jonathan Seagull



"JMB" wrote:

=SUMPRODUCT(--(LEFT(A1:A4,FIND("K",UPPER(A1:A4)&"K")-1)&".0"))

Another one that is still a few keystrokes shorter. Pretty sure I got
this
idea from one of your previous posts <g

For the OP, you might also consider using data validation to help prevent
folks from entering text. Click Data/Validation and use whole number or
decimal. This formula will only work for entries with a "K" included.


"T. Valko" wrote:

Here's another one that's a few keystrokes shorter and is normally
entered.
Assumes all entries will either be a number or a number followed by the
letter K (case doesn't matter):

1024k
1024K
256
256K

=SUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE(UPPER(A1:A5)&"K","KK","")," K",".0")))

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Worked ok for me, too.

Enter with Ctrl+Shift+Enter
Not working. I get #VALUE....

You'll get #VALUE! if don't array enter**.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Toppers" wrote in message
...
Worked OK with me with following data:

1024K
1024
789K
1234

Sum=4071




"KLZA" wrote:

On Aug 1, 4:24 pm, Toppers
wrote:
try:

=SUM(IF($D$1:$D$5<"",IF(ISNUMBER(--RIGHT($D$1:$D$5)),$D$1:$D$5,--LEFT($D$1-:$D$5,LEN($D$1:$D$5)-1))))

Enter with Ctrl+Shift+Enter



"KLZA" wrote:
Hi. I'm using the SUM() function to add cell data which
contains
bandwidth or kilobyte information. The SUM cell shows the data
in
kilobytes. The formula I'm using in the SUM cell is =SUM(CELL
RANGE)
& " KB". My problem is that the formula ignores a cell
entirely if
someone puts an alpha character after the numerics - ie 1024K
instead
of 1024. Is there a way to use the SUM() function and ignore
aplha
characters but not numerics? This way if someone types 1024K
or
1024
it still will show as 1024K in the sum field.- Hide quoted
text -

- Show quoted text -

Not working. I get #VALUE....

How to ignore text on SUM() function...
 

"JMB" wrote...
Probably could be done w/a helper column, but I don't see how to get it
all in one formula w/o ending up w/an array of arrays (for lack of a
better description). But, just because I don't see it doesn't mean it
can't be done.

Ugly, but define the name seq referring to

=ROW(INDEX(Sheet1!$1:$65536,1,1):INDEX(Sheet1!$1:$ 65536,64,1))

and use array formulas like

=SUM(--LEFT(colrange,MMULT(--ISNUMBER(-(MID(colrange,1,
TRANSPOSE(seq))&"0")),seq^0)))

I would probably resort to a macro before putting the time into finding
a single formula solution (or smack the person who put the alpha data
in and make them redo it).
....

Better to download and install Laurent Longre's MOREFUNC.XLL add-in and use

=SUM(--REGEX.MID(A1:A8,"^[-+]?((\d+(\.\d*)?)|((\d*\.)?\d+))"
&"([Ee][-+]?([1-2]?\d{1,2}|30[0-7]))?"))

which should parse any substring Excel would consider numeric with decimal
fractional part and/or scientific notation. Handling fractions, dates and
times would be a bit trickier.

How to ignore text on SUM() function...
 

"Harlan Grove" wrote in message
...
"JMB" wrote...
Probably could be done w/a helper column, but I don't see how to get it
all in one formula w/o ending up w/an array of arrays (for lack of a
better description). But, just because I don't see it doesn't mean it
can't be done.

Ugly, but define the name seq referring to

=ROW(INDEX(Sheet1!$1:$65536,1,1):INDEX(Sheet1!$1:$ 65536,64,1))

and use array formulas like

=SUM(--LEFT(colrange,MMULT(--ISNUMBER(-(MID(colrange,1,
TRANSPOSE(seq))&"0")),seq^0)))


seq = 1:64. Why 64? Seems like an arbitrary number smaller than the
"standard" arbitrary 255 because you don't expect a string longer than 64
characters.

--
Biff
Microsoft Excel MVP

How to ignore text on SUM() function...
 

Ugly, so you say, but still more efficient than anything I can come up with.
Thanks for posting a formula solution - I had tried something w/MMULT, but
couldn't get it to go and was curious to if you would come up w/something.

And, yes I agree the macro approach is quite limited.



"Harlan Grove" wrote:

"JMB" wrote...
Probably could be done w/a helper column, but I don't see how to get it
all in one formula w/o ending up w/an array of arrays (for lack of a
better description). But, just because I don't see it doesn't mean it
can't be done.

Ugly, but define the name seq referring to

=ROW(INDEX(Sheet1!$1:$65536,1,1):INDEX(Sheet1!$1:$ 65536,64,1))

and use array formulas like

=SUM(--LEFT(colrange,MMULT(--ISNUMBER(-(MID(colrange,1,
TRANSPOSE(seq))&"0")),seq^0)))

I would probably resort to a macro before putting the time into finding
a single formula solution (or smack the person who put the alpha data
in and make them redo it).
....

Better to download and install Laurent Longre's MOREFUNC.XLL add-in and use

=SUM(--REGEX.MID(A1:A8,"^[-+]?((\d+(\.\d*)?)|((\d*\.)?\d+))"
&"([Ee][-+]?([1-2]?\d{1,2}|30[0-7]))?"))

which should parse any substring Excel would consider numeric with decimal
fractional part and/or scientific notation. Handling fractions, dates and
times would be a bit trickier.

How to ignore text on SUM() function...
 

"T. Valko" wrote...
....
seq = 1:64. Why 64? Seems like an arbitrary number smaller than the
"standard" arbitrary 255 because you don't expect a string longer
than 64 characters.

The STRING may be longer than 64 chars, but the numeric substring at
the beginning would have to be no more than 64 chars. Numeric strings
of more than 64 chars aren't going to produce meaningful sums anyway.