CODEWITHSUNDEEP
excel
user3376969
user3376969

Reputation: 13

vector element operations in excel

I am trying to perform the following in one steps (one formula):

Strip a letter from a column of elements and add them up.

Example:

Data:

1x 2y 3x

I want to strip letters and add up numbers all in one formula.

I understand that I could have a helper column in which I strip letters x,y,z and then have a formula to add up the numbers, but I don't want to do this.

Thanks for any suggestions.

Upvotes: 0

Views: 206

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

Assuming one entry per cell:

Is there only one letter at the end? If so, you can use:

=SUMPRODUCT(--LEFT(A1:A100,LEN(A1:A100)-1))

If there might be multiple letters at the end, a simple UDF would be simpler:

Option Explicit
Function AddStrings(rg As Range)
    Dim L As Long
    Dim I As Long

For I = 1 To rg.Count
    L = L + Val(rg(I))
Next I

AddStrings = L
End Function

EDIT: If some of the cells might be blank, you can use either the UDF, or, if you prefer the formula, this array-entered formula:

=SUM(IFERROR(--LEFT(A1:A100,LEN(A1:A100)-1),0))

To array-enter a formula, after entering the formula into the cell or formula bar, hold down ctrl-shift while hitting enter. If you did this correctly, Excel will place braces {...} around the formula.

Upvotes: 1

stenci
stenci

Reputation: 8481

Assuming that the format is consistent, you can do something like

=VALUE(LEFT(A1,1))+VALUE(MID(A1,4,1))+VALUE(MID(A1,7,1))

If the format is not consistent, things get more difficult. Let me know and I will expand the answer.

EDIT:

This function works with a variable length text, assuming that the fields are separated by the spaces and have one letter after the number:

Function AddValues(Text As String)
  Dim Tokens() As String, I As Integer
  Tokens = Split(Text)
  For I = 0 To UBound(Tokens)
    AddValues = AddValues + Val(Left(Tokens(I), Len(Tokens(I)) - 1))
  Next I
End Function

Upvotes: 0

Related Questions