how to do calculations with values that contains both numbers and text

zack8576 · Oct 28, 2022

I have a bunch of excel files that contain item "A and B" in column K on many rows, the value in column M on these rows are something like "200"/JOINT"
I need to add up the total number from these rows.
So do I have to remove the inch mark " and the text "/JOINT" first before I can use these numbers in a math equation ?

VBA Code:

sr = 2
lr4 As Long
lrNew As Long
    Set rng2 = Range("A1").CurrentRegion
    lrNew = rng2.Cells(Rows.Count, "M").End(3).Row
        If rng2.Cells(lr4, "M").Value Like "/JOINT" Then
           With Range("M1", Cells(Rows.Count, "M").End(3))
            .Replace What:="""", Replacement:=vbNullString, LookAt:=xlPart 'REMOVE THE INCH MARK
            .Replace What:="/JOINT", Replacement:=vbNullString, LookAt:=xlPart ' REMOVE /JOINT
           End With
        End If
 
    n = WorksheetFunction.SumIfs(Range("M" & sr & ":M" & lr), Range("K" & sr & ":K" & lr), "*A and B*")

Anthony47 · Oct 29, 2022

If a formula could be enough:

Excel Formula:

=LET(dArea,IF(K2:K100="A and B",1,0),vArea,"0"&SUBSTITUTE(M2:M100,"""","") & "/",aSplit,TEXTBEFORE(vArea,"/"),SUM((aSplit)*dArea*SEQUENCE(ROWS(aSplit),,1,0)))

zack8576 · Oct 29, 2022

Anthony47 said:
If a formula could be enough:

Excel Formula:

=LET(dArea,IF(K2:K100="A and B",1,0),vArea,"0"&SUBSTITUTE(M2:M100,"""","") & "/",aSplit,TEXTBEFORE(vArea,"/"),SUM((aSplit)*dArea*SEQUENCE(ROWS(aSplit),,1,0)))

Anthony, thanks for the reply. I am using this calculation method in a macro that resides in a master file.
This master file is used to open a bunch of csv files and modify them.
I guess I can put this line of yours in the macro , and run some tests

what does the "1,0" mean in this line?

VBA Code:

(dArea,IF(K2:K100="A and B",1,0)

Anthony47 · Oct 29, 2022

I guess I can put this line of yours in the macro , and run some tests
You can use EVALUATE in your macro to get the result calculated with that formula:

VBA Code:

myValue = Evaluate("LET(dArea,IF(K2:K100=""A and B"",1,0),vArea,""0""&SUBSTITUTE(M2:M100," & String(4, Chr(34)) & ","""") & ""/"",aSplit,TEXTBEFORE(vArea,""/""),SUM((aSplit)*dArea*SEQUENCE(ROWS(aSplit),,1,0)))")

(some syntax modifications are necessary to use the formula as a string to be evaluated)

It would be better, at this point, using sheet name in the formula; thus:

VBA Code:

myValue2 = Evaluate("LET(dArea,IF(SheetA!K2:K100=""A and B"",1,0),vArea,""0""&SUBSTITUTE(SheetA!M2:M100," & String(4, Chr(34)) & ","""") & ""/"",aSplit,TEXTBEFORE(vArea,""/""),SUM((aSplit)*dArea*SEQUENCE(ROWS(aSplit),,1,0)))")

Of course you will use YOUR sheet name instead of SheetA

Excel Formula:
what does the "1,0" mean in this line?

Excel Formula:

(dArea,IF(K2:K100="A and B",1,0)

This portion of the formula assign to the "array" dArea (for "description Area) the value 1 if description="A and B" or 0 otherwise; this array will multiply the extracted values to ignore those lines whose description is <> "A and B"
However I am not sure for what you intended when you wrote "I have a bunch of excel files that contain item "A and B" in column K on many rows"; my interpretation come from reading (and interpreting) your WorksheetFunction.SumIfs line...

zack8576 · Oct 29, 2022

Anthony47 said:
I guess I can put this line of yours in the macro , and run some tests
You can use EVALUATE in your macro to get the result calculated with that formula:

VBA Code:

myValue = Evaluate("LET(dArea,IF(K2:K100=""A and B"",1,0),vArea,""0""&SUBSTITUTE(M2:M100," & String(4, Chr(34)) & ","""") & ""/"",aSplit,TEXTBEFORE(vArea,""/""),SUM((aSplit)*dArea*SEQUENCE(ROWS(aSplit),,1,0)))")

(some syntax modifications are necessary to use the formula as a string to be evaluated)

It would be better, at this point, using sheet name in the formula; thus:

VBA Code:

myValue2 = Evaluate("LET(dArea,IF(SheetA!K2:K100=""A and B"",1,0),vArea,""0""&SUBSTITUTE(SheetA!M2:M100," & String(4, Chr(34)) & ","""") & ""/"",aSplit,TEXTBEFORE(vArea,""/""),SUM((aSplit)*dArea*SEQUENCE(ROWS(aSplit),,1,0)))")

Of course you will use YOUR sheet name instead of SheetA

This portion of the formula assign to the "array" dArea (for "description Area) the value 1 if description="A and B" or 0 otherwise; this array will multiply the extracted values to ignore those lines whose description is <> "A and B"
However I am not sure for what you intended when you wrote "I have a bunch of excel files that contain item "A and B" in column K on many rows"; my interpretation come from reading (and interpreting) your WorksheetFunction.SumIfs line...

thanks for the response !
I ran this a few times, a type mismatch error kept popping up

Dave Patton · Oct 30, 2022

Some sample data and expected results would help.
Do you require a VBA solution?

T202210a.xlsm

K

L

M

N

1

2

A and B

200"/JOINT

245

3

X

15

2245

4

A and B

9"/JOINT

5

X

10lbs

6

A and B

16"/JOINT

7

X

a

8

A and B

20"/JOINT

9

A

1200"/JOINT

10

X

15

11

B

9"/JOINT

12

X

10lbs

13

A

16"/JOINT

14

X

a

15

B

1020"/JOINT

5c

Cell Formulas
Range	Formula
N2	N2	=SUM(IF(K2:K16="A and B",--LEFT(M2:M16,FIND("""",M2:M16)-1)))
N3	N3	=SUM(IF(K2:K16={"A","B"},--LEFT(M2:M16,FIND("""",M2:M16)-1)))

zack8576 · Oct 30, 2022

Dave Patton said:
Some sample data and expected results would help.
Do you require a VBA solution?

T202210a.xlsm
K L M N
1
2 A and B 200"/JOINT 245
3 X 15 2245
4 A and B 9"/JOINT
5 X 10lbs
6 A and B 16"/JOINT
7 X a
8 A and B 20"/JOINT
9 A 1200"/JOINT
10 X 15
11 B 9"/JOINT
12 X 10lbs
13 A 16"/JOINT
14 X a
15 B 1020"/JOINT
5c
Cell Formulas
Range Formula
N2 N2 =SUM(IF(K2:K16="A and B",--LEFT(M2:M16,FIND("""",M2:M16)-1)))
N3 N3 =SUM(IF(K2:K16={"A","B"},--LEFT(M2:M16,FIND("""",M2:M16)-1)))

Dave
see dropbox link for example test file, also see below for code.
the part of the code that was supposed to remove the inch mark and test was skipped over when I was stepping through it.
It appears that the code thinks the keyword listed in the code does not exist when it clearly does

VBA Code:

Sub SealantConseal()
    Dim lrNew As Long
    lrNew = ActiveSheet.Range("M" & Rows.Count).End(xlUp).Row
    sr = 2
    Set rng2 = Range("M1").CurrentRegion
    lrNew = Cells(Rows.Count, "M").End(3).Row
        If Cells(lrNew, "M").Value Like "*JOINT*" Then
           With Range("M1", Cells(Rows.Count, "M").End(3))
            .Replace What:="""", Replacement:=vbNullString, LookAt:=xlPart 'REMOVE THE INCH MARK
            .Replace What:="/JOINT", Replacement:=vbNullString, LookAt:=xlPart ' REMOVE /JOINT
           End With
        End If
    lrNew = lrNew + 1
    n = WorksheetFunction.SumIfs(Range("M" & sr & ":M" & lr), Range("K" & sr & ":K" & lr), "*Joint Sealant*")
 
    Cells(lrNew, "A") = Cells(lr, "A")
    Cells(lrNew, "B") = "."
    Cells(lrNew, "C") = n / 12 / 14.5
    Cells(lrNew, "D") = "F51019"
    Cells(lrNew, "I") = "Purchased"
    Cells(lrNew, "K") = "CS-102 Sealant"

   
    If Cells(lrNew, "C").Value Like "*0*" Then
        Cells(lrNew, 4) = ","
    End If
End Sub

2022070041 sanitary 2.csv

Shared with Dropbox

www.dropbox.com

Anthony47 · Oct 30, 2022

I suggest that you modify the first part of your macro as folllows:

VBA Code:

Sub SealantConseal()
    Dim lrNew As Long
    lrNew = ActiveSheet.Range("M" & Rows.Count).End(xlUp).Row
    sr = 2
    Set rng2 = Range("M1").CurrentRegion
    lrNew = Cells(Rows.Count, "M").End(xlUp).Row
'        If Cells(lrNew, "M").Value Like "*JOINT*" Then
           With Range("M1:M" & lrNew)
            .Replace What:="""", Replacement:=vbNullString, LookAt:=xlPart 'REMOVE THE INCH MARK
            .Replace What:="/JOINT", Replacement:=vbNullString, LookAt:=xlPart ' REMOVE /JOINT
           End With
'        End If
    lrNew = lrNew + 1
    n = WorksheetFunction.SumIfs(Range("M" & sr & ":M" & lrNew), Range("K" & sr & ":K" & lrNew), "*Joint Sealant*")

This will remove the quotation marks and /JOINT from column M and will properly calculate the value for n (the last line in the code)

As far as the next lines is concerned:

VBA Code:

    Cells(lrNew, "A") = Cells(lr, "A")
    Cells(lrNew, "B") = "."
    Cells(lrNew, "C") = n / 12 / 14.5
    Cells(lrNew, "D") = "F51019"
    Cells(lrNew, "I") = "Purchased"
    Cells(lrNew, "K") = "CS-102 Sealant"

  
    If Cells(lrNew, "C").Value Like "*0*" Then
        Cells(lrNew, 4) = ","
    End If
End Sub

I don't know what is their objective and thus cannot suggest anything. However:
1) since lrNew is calculated on column M you will overwrite some of the lines currently in your table
2) variable "lr" has never been calculated, so the first instruction will generate an error

To avoid problem #1 (if it is really a problem) I think that calculating lrNew on the content of column A would be ok:

VBA Code:

    lrNew = Cells(Rows.Count, "A").End(xlUp).Row

As far as problem #2, I have no idea about what lr should be, thus cannot suggest anything

how to do calculations with values that contains both numbers and text

zack8576

Active Member

Excel Facts

Anthony47

Well-known Member

zack8576

Active Member

Anthony47

Well-known Member

zack8576

Active Member

Dave Patton

Well-known Member

zack8576

Active Member

2022070041 sanitary 2.csv

Anthony47

Well-known Member

Similar threads

Forum statistics

Share this page

how to do calculations with values that contains both numbers and text

Active Member

Excel Facts

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock