excel - Parse string and total the positive & negative numeric components -


i have string in excel cell.

each row represents sentence. string represents positive , negative score word in sentence.

sentences can of length, e.g.

joy: pos=0.37 neg=0.0, honest: pos=0.4 neg=0.0, pick: pos=0.0 neg=0.0
hello: pos=0.0 neg=0.0, ok: pos=0.0 neg=0.0

i want calculate total positive , negatives in cell.

without splitting string text columns feature in excel, don't know possible way formula.

an example of output in case be:

pos=0.77 neg=0.0
pos=0.0 neg=0.0

any ideas?

there may better way believe work you:

function pos(rtest range) string  dim a() string dim integer dim ival double dim wf worksheetfunction set wf = application.worksheetfunction  = split(rtest, ",")  dim istart integer dim iend integer  = lbound(a) ubound(a)     istart = wf.find("=", a(i)) + 1     iend = instr(wf.find("=", a(i)) + 1, a(i), " ")      ival = ival + cdbl(mid(a(i), istart, iend - istart)) next  pos = "pos=" & cstr(ival)  end function  function neg(rtest range) string  dim a() string dim integer dim ival double dim wf worksheetfunction set wf = application.worksheetfunction  = split(rtest, ",")  dim istart integer dim iend integer  = lbound(a) ubound(a)     istart = instrrev(a(i), "=") + 1     iend = len(a(i)) + 1     ival = ival + cdbl(mid(a(i), istart, iend - istart)) next  neg = "neg=" & cstr(ival)  end function 

i still consider myself new vba. i'm sure optimized or tightened bit. put 2 functions in vba module. put =pos or =neg accordingly regular formula in cell , put range in.


Comments

Popular posts from this blog

javascript - Laravel datatable invalid JSON response -

java - Exception in thread "main" org.springframework.context.ApplicationContextException: Unable to start embedded container; -

sql server 2008 - My Sql Code Get An Error Of Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '8:45 AM' to data type int -