Mar 01 2022 05:35 AM
%92 COTTON %8 ELASTANE & |
COTTON %73,POLYESTER %14,POLYAMIDE %11,ELASTANE %2 |
samples.
It needs 100% verification. how can i do?
thanks
Mar 01 2022 06:29 AM
Here is a custom VBA function you can use:
Function Verify(ByVal txt As String) As Boolean
Dim arr() As String
Dim i As Long
Dim s As Long
txt = Replace(txt, ",", " ")
txt = Replace(txt, "%", " ")
arr = Split(txt)
For i = 0 To UBound(arr)
s = s + Val(arr(i))
Next i
Verify = (s = 100)
End Function
You can use this in a cell formula:
=Verify(A2)
will return TRUE if the numbers in A2 add up to 100, FALSE if not. The formula can be filled down.
Or you can use it in a conditional formatting rule of type 'Use a formula to determine which cells to format', to highlight cells that meet the condition (or not).
Mar 01 2022 01:52 PM
Mar 01 2022 03:44 PM
I think is is time to give in!
I used Charles Williams's regex add-in
= MAP(target, LAMBDA(t, SUM(--Rgx.MID(t,"[\d\.]+",0))))
and adapted the formula OK for decimal points.
Using basic functionality with MID and SCAN, I achieved a solution of sorts for integers
= MAP(target,
LAMBDA(t,
LET(
seq, SEQUENCE(1,LEN(t)),
chr, MID(t,seq,1),
digit, IF(ISNUMBER(--chr), --chr),
build, SCAN(,digit, LAMBDA(p,d,IF(d,IF(p,10*p+d,d)))),
reverse, INDEX(build, 1+LEN(t)-seq),
num, SCAN(,reverse, LAMBDA(p,n,IF(n,IF(NOT(p),n)))),
SUM(num)
)
)
)
but I don't think I fancy further adjustment.
Mar 01 2022 11:12 PM - edited Mar 01 2022 11:16 PM
If the data reliably included the leading "%" signs, you maybe could also filter out those elements and then combine them back into an expression.
Function PctTotal(ByVal txt As String) As Single
Dim arr As Variant
arr = Split(Replace(txt, ",", " "))
arr = Join(Filter(arr, "%"), "+")
PctTotal = CSng(Evaluate(Replace(arr, "%", "")))
End Function