Home

Addition resulting in 0 after using formula to remove alpha characters

JoeCavasin
Occasional Contributor

Need assistance if possible.

I have a data set that is exported as digits, preceded by a letter, in this case F.  This export format is hard wired into the program, so the alpha/numeric combo will remain in the dataset with every export and doesn't change letters or spacing, etc.  I can already utilize either TRIM or RIGHT formulas to remove the preceding letter, and leave the raw numbers in a set of helper columns.  However, I need to add these items together once trimmed of alpha characters and nothing has been able to return a result other than an error (0, #Value or #Error).  Anyone have any thoughts?  attached a basic sample of this set up for reference

 

 

 

2 Replies
If the addends are in F2:I2, the formula in J2 is:
=SUMPRODUCT(—F2:I2)
The double negatives before the range converts the values therein to numbers.

@JoeCavasin , you may use

=SUMPRODUCT(VALUE(G2:J2))

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
36 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies