How to sum cell with any non numeric characters

[prajay]

FOR EXAMPLE

1000W.	1000W	1000D.	1000E	4000

<tbody>
</tbody>

HOW TO SUM ABOVE CASE TO GET ANSWER 4000 WITHOUT REMOVING TEXT & DOT OR ANY NON ALPHANUMERIC CHARACTER

GIVE ME VBA CODE OR SIMPLE FORMULA ...

 

[jtakw]

Hi,

We need more information, are those values All in one cell, in 4 separate cells, will there be more cells, are they Always 4 digits, are the Alpha Always "W", "D", "E", and "."???

BTW, you need to get your keyboard fixed, I think your Caps Lock button is stuck?

 

[prajay]

Sorry

all values are in separate cells no there is always different alpha characters along with dot somtimes

for example

A1	B1	C1	D1	total
1000W.	1000d	1000c.	1000o	4000

<tbody>
</tbody>

 

[p85ki]

Following u to jtakw, if it is always 4 numbers with a character at the end, the simplist formula will be:

A1: 1000A
B1: 1000B

Formula: =SUM(VALUE(LEFT(A1,4))+VALUE(LEFT(B1,4)))

OR

A1: A1000
B1: B1000

Formula =SUM(VALUE(RIGHT(A1,4))+VALUE(RIGHT(B1,4)))

As there is text, i'm never sure if by putting the LEFT/RIGHT formula will convert this to a number, so I always add VALUE to ensure for certain this is always referred as a number, and always sum as well. You can write both formulas without the sum/value - so you have RIGHT/LEFT.

 

[jtakw]

You didn't answer the question if it's Always 4 digits, so use this, you can expand the Range of cells to suit:

Book1
	A	B	C	D	E
1					Total
2	1000W.	1000d	1000c.	1000o	4000
3	1000W.	1000d	1200c.	1000o	4200
Sheet222

Cell Formulas
Range	Formula
E2	=SUMPRODUCT(LEFT(A2:D2,4)*1)

 

[prajay]

Following u to jtakw, if it is always 4 numbers with a character at the end, the simplist formula will be:

A1: 1000A
B1: 1000B

Formula: =SUM(VALUE(LEFT(A1,4))+VALUE(LEFT(B1,4)))

OR

A1: A1000
B1: B1000

Formula =SUM(VALUE(RIGHT(A1,4))+VALUE(RIGHT(B1,4)))

As there is text, i'm never sure if by putting the LEFT/RIGHT formula will convert this to a number, so I always add VALUE to ensure for certain this is always referred as a number, and always sum as well. You can write both formulas without the sum/value - so you have RIGHT/LEFT.

Sometimes there is 3 or 2 or 4 or 5 or 6 numbers then how to sum....?

 

[jtakw]

Following u to jtakw, if it is always 4 numbers with a character at the end, the simplist formula will be:

A1: 1000A
B1: 1000B

Formula: =SUM(VALUE(LEFT(A1,4))+VALUE(LEFT(B1,4)))

OR

A1: A1000
B1: B1000

Formula =SUM(VALUE(RIGHT(A1,4))+VALUE(RIGHT(B1,4)))

As there is text, i'm never sure if by putting the LEFT/RIGHT formula will convert this to a number, so I always add VALUE to ensure for certain this is always referred as a number, and always sum as well. You can write both formulas without the sum/value - so you have RIGHT/LEFT.

Just FYI, you don't need the + if you're using SUM, or you don't need the SUM if you're using +

=SUM(VALUE(LEFT(A1,4)),VALUE(LEFT(B1,4)))
=VALUE(LEFT(A1,4))+VALUE(LEFT(B1,4))

 

[mikerickson]

You could use this UDF with a formula like

=SUM(FirstValue(A1:D1))

Function FirstValue(inRange As Range) As Variant
    Dim Result As Variant
    Dim i As Long, j As Long
    With inRange
        If .Cells.Count = 1 Then
            Result = Val(CStr(.Cells(1, 1).Value))
        Else
            Result = .Value
            For i = 1 To .Rows.Count
                For j = 1 To .Columns.Count
                    Result(i, j) = Val(CStr(Result(i, j)))
                Next j
            Next i
        End If
    End With
    FirstValue = Result
End Function

This approach will handle data that has varying lengths of leading numerals.

 

[prajay]

You didn't answer the question if it's Always 4 digits, so use this, you can expand the Range of cells to suit:

	A	B	C	D	E
1					Total
2	1000W.	1000d	1000c.	1000o	4000
3	1000W.	1000d	1200c.	1000o	4200

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet222​

Worksheet Formulas Cell Formula E2 =SUMPRODUCT(LEFT(A2:D2,4)*1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

thank you its working fine if some times different digits like 2 or 3 or 4

	A	B	C	D	E
1					Total
2	1000W.	1000d	10c.	999o	?
3	1000W.	100d	999c.	h	?

<colgroup><col width="25px" style="background-color: rgb(218, 231, 245);"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

A	B	C	D	E
1					Total
2	1000W.	1000d	1000c.	1000o	4000
3	1000W.	1000d	1200c.	1000o	4200

<thead>
</thead><tbody>
</tbody>

Sheet222​

</body>

 

[prajay]

You could use this UDF with a formula like

=SUM(FirstValue(A1:D1))

Function FirstValue(inRange As Range) As Variant
    Dim Result As Variant
    Dim i As Long, j As Long
    With inRange
        If .Cells.Count = 1 Then
            Result = Val(CStr(.Cells(1, 1).Value))
        Else
            Result = .Value
            For i = 1 To .Rows.Count
                For j = 1 To .Columns.Count
                    Result(i, j) = Val(CStr(Result(i, j)))
                Next j
            Next i
        End If
    End With
    FirstValue = Result
End Function

This approach will handle data that has varying lengths of leading numerals.

Its working fine with all condition ... thank you so much