# How to sum cell with any non numeric characters

#### prajay

##### New Member
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

##### Well-known Member
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?

Last edited:

#### prajay

##### New Member
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

##### New Member
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

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

#### prajay

##### New Member
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

##### Well-known Member
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))

Last edited:

#### mikerickson

##### MrExcel MVP
You could use this UDF with a formula like

=SUM(FirstValue(A1:D1))

Code:
``````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.

Last edited:

#### prajay

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

ABCDE
1Total
21000W.1000d1000c.1000o4000
31000W.1000d1200c.1000o4200

</tbody>
Sheet222

Worksheet Formulas
CellFormula
E2=SUMPRODUCT(LEFT(A2:D2,4)*1)

</tbody>

<tbody>
</tbody>
thank you its working fine if some times different digits like 2 or 3 or 4

ABCDE
1Total
21000W.1000d10c.999o?
31000W.100d999c.h?

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

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
ABCDE
1Total
21000W.1000d1000c.1000o4000
31000W.1000d1200c.1000o4200

</tbody>
Sheet222
</body>

#### prajay

##### New Member
You could use this UDF with a formula like

=SUM(FirstValue(A1:D1))

Code:
``````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

1,082,335
Messages
5,364,696
Members
400,811
Latest member
MSBINinja

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...