Ignore Text in a Cell and sum only the Value

**Rene777** · 04-08-2013, 06:06 PM

Hello - I'm basically trying to add two, simple values, 1 and 2. The first, A1, is Always Negaitve and the second, A2, is Always Positive. So...

If:

A1=1 and A2= 1 then A3 = 0
A1=2 and A2= 2 then A3 = 0

A1=1 and A2= 0 then A3 = -1
A1=2 and A2= 0 then A3 = -2

A1=0 and A2= 1 then A3 = 1
A1=1 and A2= 2 then A3 = 1

These 6 examples will be the only possible entries.

The tricky part is that sometimes a cell will contain a "v" as in "1v" or "2v" and I need those to read as 1 or 2, respectively. This is needed for later search functions. Is there a way to use a custom number format to make 1v or 1 = 1, and 2v or 2 = 2... so that excel reads either entry as only the numbers 1 and 2?

Or, is it possible to us a function like =SUMPRODUCT in A3 to get the outcome I am needing?

I appreciate any guidance...I do not have a workbook currently because I do not have any working formulas that would give accurate examples.

Thank you...

**mehmetcik** · 04-08-2013, 06:12 PM

Hi Rene

I think you need a user defined function.

It is quite easy to create one.

Can you wait ten minutes?

While waiting see if you can find the developer tab and open visual basic.

**mehmetcik** · 04-08-2013, 06:28 PM

This function will remove any text in a string and convert it into a number. It can be modified to accept commas and full stops if you need that.

Please Login or Register  to view this content.

**FDibbins** · 04-08-2013, 06:31 PM

If you ONLY have those 2 valuesm AND that is the format they will be in (1v or 1 etc), and they are in A1 and B1. try this, copied down...

=LEFT(A1,1)*-1+LEFT(B1,1)*1

This should allow for both values and text

**Rene777** · 04-08-2013, 06:36 PM

Thank you both!

MehMetCik - where do I enter your formula in VBA? You may have to walk me through that one....seems to be a bit over my head :-) And, can I make it apply to a certain range, or does that affect the entire workbook?

FDibbins - your formula works well when there are values present, but when there are no values present in either, or both, of the cells, then there is an error message. Is there a way to correct this issue? I would ultimately like the destination cell to remain blank if there are no entries in both of the corresponding data cells. And, I would like to sometimes only make an entry in either one of the input cells, and have the other remain blank, and still allow your formula to work in the destination cell...is that possible?

Thank you!

**FDibbins** · 04-08-2013, 07:29 PM

sure, change it to this...

=IF(OR(A1="",B1=""),"",LEFT(A1,1)*-1+LEFT(B1,1)*1)

**mehmetcik** · 04-08-2013, 07:49 PM

Hi

If you know how to open vba a then you can put it in any standard module.

The function will be available anywhere in the workbook.

I will send you a sample.

**Tony Valko** · 04-08-2013, 08:03 PM

Can you post a SMALL** sample file that shows us your data and the result(s) you expect?

** SMALL = about 20 rows (or less) worth of data.

**Rene777** · 04-08-2013, 09:22 PM

Hey All - thanks again for all the help. I think I'm on the right track with FDibbins formulas, they seem like the most simple approach, so far...Almost There! :-)

The last if/or formula works if both data cells are blank...But, If one has value and the other one is blank, then the Destination Cell remains blank. I could use a zero in the Data Cells, but in order to save keystrokes, and for aesthetics, it would be better if I could leave them blank when I don't need to enter a value in one of the Data Cells.

So, If:

A1 = Blank
and
A2 = 2v
then
A3 = 2

OR

If:

A1 = 2
and
A2 = Blank
then
A3 = -2

But, if both A1 and A2 are blank then A3 remains blank...And, of course, it still needs to add like the first example at the top of this post...

Any thoughts? lol

**FDibbins** · 04-09-2013, 12:13 AM

=IF(A1="",0,LEFT(A1,1)*-1)+if(b1="",0,LEFT(B1,1)*1))

**Rene777** · 04-09-2013, 12:21 AM

Hey FDibbins - I appreciate all the help... I copied and pasted that formula exactly, but it doesn't add - Only leaves a "0" in the Destination Cell whether there is data in both Input Cells, one of the cells, or neither....

**Rene777** · 04-10-2013, 11:44 PM

Hello Again - I cleared some old formatting from my data cells and your formula works really well. Only ONE last finishing touch :-) lol

How to make the destination cell empty if there are no values yet entered in BOTH of the data cells. So, if A1 is blank AND B1 is blank, I need C1 to remain blank until data is entered in either cell...

I'm thinking something like:

=if(and(A1="",B1=""),"",left(A1,1)*-1+left(B1,1)*1)

I'm getting an error message that says value is of the wrong data type on this particular formula when only one of the data cells is blank, if both are blank it works, if both have data it works...perhaps if I throw in an If ISERROR at the beginning somehow...

Any thoughts?

Thanks Again!

**FDibbins** · 04-11-2013, 12:26 AM

I think its time for you to upload a sample workbook, so we can stop guessing at answers for you
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**Rene777** · 04-11-2013, 12:37 AM

There you go...

**FDibbins** · 04-11-2013, 12:44 AM

based on your sample, i used my suggested formula from post #10...
=IF(A1="",0,LEFT(A1,1)*-1)+IF(B1="",0,LEFT(B1,1)*1)

copied down, it gave me...
1
2
-2

what answers where you expecting?

**Rene777** · 04-11-2013, 01:06 AM

Hey FD - yeah, it works really well (actually the answer in your example should be 1) Like I mentioned in post #12 - I just needed a way to leave the Destination Cell (C1) blank until data is entered in either A1 or B1 - right now, C1 would have a "0" if there is no data entered.

I have attempted to use Cell Formatting to solve that problem in the past, but that has created other issues within the formulas, themselves. So, I need to find a way to do it within the formula in C1.

Thanks!

**Rene777** · 04-11-2013, 03:50 PM

Finally figured it out :-)

=IF(AND(B5="",B6=""),"",IF(B5="",0,LEFT(B5,1)*-1)+IF(B6="",0,LEFT(B6,1)*1))

Thanks guys!

**Tony Valko** · 04-11-2013, 04:27 PM

This is a few keystrokes shorter:

=IF(B5&B6="","",IF(B5="",0,LEFT(B5)*-1)+IF(B6="",0,LEFT(B6)*1))

**Trevasaurus** · 04-11-2013, 05:14 PM

Hey Rene,

FYI, to use the UDF that mehmetic gave you,

press AULT + F11 This will bring up the VBA editor

Click in 'Insert' and click 'Module'

Paste the UDF code into the Module window that opens up.

Save your document and you're ready to use the UDF. Use it just like you would a regular formula.

=numeric(A:2)

Ignore Text in a Cell and sum only the Value

LinkBack

Thread Tools

Rate This Thread

Display

Ignore Text in a Cell and sum only the Value

Re: Ignore Text in a Cell and sum only the Value

Re: Ignore Text in a Cell and sum only the Value

Re: Ignore Text in a Cell and sum only the Value

Re: Ignore Text in a Cell and sum only the Value

Re: Ignore Text in a Cell and sum only the Value

Re: Ignore Text in a Cell and sum only the Value

Re: Ignore Text in a Cell and sum only the Value

Re: Ignore Text in a Cell and sum only the Value

Re: Ignore Text in a Cell and sum only the Value

Re: Ignore Text in a Cell and sum only the Value

Re: Ignore Text in a Cell and sum only the Value

Re: Ignore Text in a Cell and sum only the Value

Re: Ignore Text in a Cell and sum only the Value

Re: Ignore Text in a Cell and sum only the Value

Re: Ignore Text in a Cell and sum only the Value

Re: Ignore Text in a Cell and sum only the Value

Re: Ignore Text in a Cell and sum only the Value

Re: Ignore Text in a Cell and sum only the Value

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions