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
Post a Comment