CODEWITHSUNDEEP
excelexcel-formularangeformula
user5316498
user5316498

Reputation:

Do math on cells also contaning text in EXCEL

I have a range of cells which I want to do some math on. But I also want those cells to contain some text.

For instance I want the sum of A1 and B1 where A1 contains the number 10 and "z001" and B1 contains the number 20 and "Z004".

Then I want the formula to ignore the text, and just come up with 30.

Is this possible?

Upvotes: 1

Views: 3134

Answers (2)

Catie Camastro
Catie Camastro

Reputation: 80

For a quick solution, type "=Left(A1, 2) + Left(B1, 2)" into C1. Drag this equation down the rest of your range and you should get the results you want, provided the numbers you are adding are all 2 digits.

You can also use VBA if you need to run the same equation on multiple cells.

If you can get the same results by just removing the letters, try:

For i = 58 To 127
'Change out str with the variable name you have assigned to your cell value.
str = Replace(str, Chr(i), "")
Next i

58 and 127 represent the first and last positions in a range of characters on the Ascii table that are not numerals http://www.asciitable.com/

If you just want to include the first two numbers of each cell in your equation and ignore the "Z00#", you can try: 

strLeft = Left(str, 2)

This will reduce your string down to the first two characters of each cell.

You can look here for other ways to remove characters you don't want. http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=269:excel-vba-string-functions-left-right-mid-len-replace-instr-instrrev&catid=79&Itemid=475

Here is in example of how you would implement something like this with simple addition.

Dim a as range
Dim b as range
Dim aLeft as integer
Dim bLeft as integer
Dim cleft as integer

a = Worksheets("WorksheetName").Cells(A1).Value
b = Worksheets("WorksheetName").Cells(B1).Value

aLeft = Left(a, 2)
bLeft = Left(b, 2)

cLeft = aLeft + bLeft

Worksheets("WorksheetName").Cells(C1).Value = cLeft

This would add the first two digits of cells A1 and B1 then display the result in C1.

Upvotes: 1

Victor
Victor

Reputation: 1205

As I see it, you have 2 options:

  1. Search for the number within each cell and sum it up (see below).
  2. Split the columns to have 1 column of numbers and one of codes (e.g. "z001"). See on the "Data" tab on the ribbon and click "Text to Columns" on the Data Tools group.

The first option would be the quickest and more straightforward. You need to make a third column where the sum will be and then use, for example, the function LEFT. This function allows you to retrieve characters from a cell. See example below:

enter image description here

To get "30" I have used the following formula on C2: 

=LEFT(A2,2)+LEFT(B2,2)

Note this is not ideal since this formula is looking for 2 characters every time. If you have a scenario with the following code "5z005" it won't work because it will try to sum "5z" as if it was a number. In that case you're better off finding a pattern (code = "number" "z" "number") and splitting the columns as I said on option 2.

Upvotes: 0

Related Questions