How to sum cell with any non numeric characters

[prajay]

FOR EXAMPLE
[TABLE="width: 500"]
<tbody>[TR]
[TD]1000W.
[/TD]
[TD]1000W

[/TD]
[TD]1000D.

[/TD]
[TD]1000E

[/TD]
[TD]4000

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]A1[/TD]
[TD]B1[/TD]
[TD]C1[/TD]
[TD]D1[/TD]
[TD]total[/TD]
[/TR]
[TR]
[TD]1000W.[/TD]
[TD]1000d[/TD]
[TD]1000c.[/TD]
[TD]1000o[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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
Total
1000W.	1000d	1000c.	1000o
1000W.	1000d	1200c.	1000o

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]4000[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]4200[/TD]

</tbody>

Sheet222​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=SUMPRODUCT(LEFT(A2:D2,4)*1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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

Total
1000W.	1000d	10c.	999o
1000W.	100d	999c.	h

<colgroup><col width="25px" style="background-color: rgb(218, 231, 245);"><col><col><col><col><col></colgroup><thead>
[TH="align: center"][/TH]
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]
[TH="align: center"]D[/TH]
[TH="align: center"]E[/TH]

</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]?[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]?[/TD]

</tbody>

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

A	B	C	D	E
Total
1000W.	1000d	1000c.	1000o
1000W.	1000d	1200c.	1000o

<thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]4000[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]4200[/TD]

</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