Formula to parse string then sum values

[Drew]

Hello All,

I've been looking around for solutions to this type of scenario, and have found different variants, but haven't found anything that suits my needs...

I have strings that contains values (accounting variance descriptions with abbreviated numbers)... what I'm looking for is to parse the numbers then sum the numbers but there's a catch, there's always a catch...

for instance, take this string...

"mainly due to Clearing $150.3M / Operating NIB -$71.5M / Collections Receivable $1,470.5B / Cash and Due From Banks -$84.3K"

There could be positive and negative values.

Now, we can't just sum each number to get a total because the values are formatted ie (K) thousands, (M) millions and (B) Billions. I believe we have to parse the numbers then reallocate the value by multiplying by a variable ie. If a number ends in "K", then multiply by 1,000
if a number ends in "M", then multiply by 1,000,000
if a number ends in "B", then multiply by 1,000,000 (this stays at 1,000,000 because the actual format was extended ie $1,470.5B not $1.5B (rounded)).

Then the sum of those converted numbers to get a total.

I thought about creating three separate formulas in one cell where they add together but I still haven't come up with a one or three formula solution.

Hope this makes sense....

Thanks.

 

[JamesCanale]

365 only of course:

MrExcelPlayground18.xlsx
	A	B
2	mainly due to Clearing $150.3M / Operating NIB -$71.5M / Collections Receivable $1,470.5B / Cash and Due From Banks -$84.3K	$1,549,215,700.00
Sheet22

Cell Formulas
Range	Formula
B2	B2	=LET(z,A2,a,SUBSTITUTE(z,"$",""),b,TEXTSPLIT(a,," "),c,CODE(b),d,FILTER(b,(c=45)+(c>47)*(c<58)),e,VALUE(LEFT(d,LEN(d)-1)),f,RIGHT(d,1),g,IFS(UPPER(f)="K",1000,UPPER(f)="M",1000000,UPPER(f)="B",1000000),SUM(e*g))

 

[Fluff]

Another option for 365

Fluff.xlsm
	A	B
1
2	mainly due to Clearing £150.3M / Operating NIB -£71.5M / Collections Receivable £1,470.5B / Cash and Due From Banks -£84.3K	1,470,578,715,700.00
Main

Cell Formulas
Range	Formula
B2	B2	=LET(x,TEXTAFTER(TRIM(TEXTSPLIT(A2,,"/"))," ",-1),SUM(TEXTBEFORE(x,{"K","M","B"})*SWITCH(RIGHT(x),"M",10^6,"K",10^3,"B",10^9,1)))

@kweaver I think you are missing a few 0s for the Billion.

 

[Drew]

Thank you both!
Both formulas worked, although I had a slight tweak to Fluff's formula, from "B",10^9 to "B",10^6
I really have to start learning these new excel 365 formulas, very impressive.
Thanks again.

 

[Fluff]

Missed the fact that the billions are actually millions.

Glad we could help & thanks for the feedback.

 

[Bosquedeguate]

Here is a VBA function that will parse an sum values from your example string

Function ParseNSum(rng As Range, Optional Delimiter As String)

''"mainly due to Clearing $150.3M / Operating NIB -$71.5M / Collections Receivable $1,470.5B / Cash and Due From Banks -$84.3K"
  Dim tmp
  Dim total As Double
  Dim exponent As Integer
  Dim dlmt As String
  Dim str As String
  
  dlmt = Delimiter
  If Len(dlmt) = 0 Then
    dlmt = ","
  End If
  
  str = rng.Value
  If Len(str) > 0 Then
    'Parse the string
    tmp = Split(str, dlmt)
    
    'Loop through parsed array and get the last value for each substring
    total = 0
    For i = 0 To UBound(tmp)
    
      'Find the last substring (assumes a value) in each parsed substring
      'Strip any unnecessary spaces
      'Remove the $
      valstr = Replace(Trim(Mid(tmp(i), InStrRev(Trim(tmp(i)), " ") + 1)), "$", "")
        
      'Make sure we have a number
      
      Select Case UCase(Mid(valstr, Len(valstr), 1))
        Case "K": exponent = 3
        Case "M": exponent = 6
        Case "B": exponent = 9
        Case "T": exponent = 12
        Case Else: exponent = 0
      End Select
      total = total + Val(Replace(valstr, ",", "")) * (10 ^ exponent)
    Next i
  End If
  ParseNSum = total
End Function

Test spreadsheet. More testing should be done.

GetLastRow.xlsm
	A	B	C	D	E	F	G	H	I	J
15	mainly due to Clearing $150.3M / Operating NIB -$71.5M / Collections Receivable $1,470.5B / Cash and Due From Banks -$84.3K
16	1.47058E+12
Sheet1

Cell Formulas
Range	Formula
A16	A16	=parsensum(A15,"/")

 

[Fluff]

Looks like you missed this

if a number ends in "B", then multiply by 1,000,000 (this stays at 1,000,000 because the actual format was extended ie $1,470.5B not $1.5B (rounded)).

The same as I did.

 

[Bosquedeguate]

Just change the “9” to a “6” for the billion case. Or end the source record and don’t change the VBA code.

 

[Bosquedeguate]

Edit (not end) the VBA code