Summing cells that contain numbers and text

Hello-
I'm trying to sum columns that contain mostly number values, but that also
contain some occasional text. Excel, of course, ignores those cells that
contain text. I've noticed other users with similar questions, but I'm still
not getting it. The text does not have any quantitative value. It is merely a
visual tag that I want to print along with the number value. Is there a way
to make Excel ignore the text but still add the data? Perhaps a formula for
each cell that contains text + data that makes the SUM formula "see" only the
data for that cell? Please dumb it down for me, I'm new to Excel. Thanks.

Do you mean like

1
2
a
3
c
etc

then SUM(Range)

will ignore the text values, but if your value are like

1a
2
3b
etc

then you can't sum unless you can extract the number but then the question
is what are the different text
strings, where in the cells are they (before/after number)?

If the latter you should rethink your design and tag the values in separate
cells, it's not a good design if you can't calculate numbers without first
going to length in extracting them from text first

Note that you can use a custom format in the cells like

0.00 "tag"

then it will display as 12.25 tag
but the value would be numerical


--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com


"Mango" <[email protected]> wrote in message
news:[email protected]...
> Hello-
> I'm trying to sum columns that contain mostly number values, but that also
> contain some occasional text. Excel, of course, ignores those cells that
> contain text. I've noticed other users with similar questions, but I'm
> still
> not getting it. The text does not have any quantitative value. It is
> merely a
> visual tag that I want to print along with the number value. Is there a
> way
> to make Excel ignore the text but still add the data? Perhaps a formula
> for
> each cell that contains text + data that makes the SUM formula "see" only
> the
> data for that cell? Please dumb it down for me, I'm new to Excel. Thanks.

Although awkward, you can append the Letters to the numbers in a cell with
Custom Formatting........Right-click on the cell, choose FormatCells >
NumberTab > Custom > and in the Type: window, put 0 A (that's zero and A, or
whatever letter you wish to append) > OK.....then type only the number
portion in the cell.

Vaya con Dios,
Chuck, CABGx3



"Mango" wrote:

> Hello-
> I'm trying to sum columns that contain mostly number values, but that also
> contain some occasional text. Excel, of course, ignores those cells that
> contain text. I've noticed other users with similar questions, but I'm still
> not getting it. The text does not have any quantitative value. It is merely a
> visual tag that I want to print along with the number value. Is there a way
> to make Excel ignore the text but still add the data? Perhaps a formula for
> each cell that contains text + data that makes the SUM formula "see" only the
> data for that cell? Please dumb it down for me, I'm new to Excel. Thanks.

Well, let me give you an example of one of these cells. Say the value I want
added in the cell is 550, but I want to also write in the cell that there is
a fee associated with it, like a late fee. Then I would write in the cell:
"550 + late fee", or "550 (late fee). The problem with adding an extra cell
for text, for each column, is it would I think complicate the formatting, or
make the worksheet too big to print on one page. I'm already maxed out as far
as width in landscape mode. I guess I'm wanting a Word table with Excel
functionality, and it's not working out. These cells that share the text are
few and far between, so if there is a formula that I can enter to extract the
data from the text for each instance, it wouldn't be such a bad workaround.

"Peo Sjoblom" wrote:

> Do you mean like
>
> 1
> 2
> a
> 3
> c
> etc
>
> then SUM(Range)
>
> will ignore the text values, but if your value are like
>
> 1a
> 2
> 3b
> etc
>
> then you can't sum unless you can extract the number but then the question
> is what are the different text
> strings, where in the cells are they (before/after number)?
>
> If the latter you should rethink your design and tag the values in separate
> cells, it's not a good design if you can't calculate numbers without first
> going to length in extracting them from text first
>
> Note that you can use a custom format in the cells like
>
> 0.00 "tag"
>
> then it will display as 12.25 tag
> but the value would be numerical
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> http://nwexcelsolutions.com
>
>
> "Mango" <[email protected]> wrote in message
> news:[email protected]...
> > Hello-
> > I'm trying to sum columns that contain mostly number values, but that also
> > contain some occasional text. Excel, of course, ignores those cells that
> > contain text. I've noticed other users with similar questions, but I'm
> > still
> > not getting it. The text does not have any quantitative value. It is
> > merely a
> > visual tag that I want to print along with the number value. Is there a
> > way
> > to make Excel ignore the text but still add the data? Perhaps a formula
> > for
> > each cell that contains text + data that makes the SUM formula "see" only
> > the
> > data for that cell? Please dumb it down for me, I'm new to Excel. Thanks.
>
>
>

CLR-
I'm sorry, I don't understand. Will Excel print the number + text in the
cell, and add the number value in the SUM formula?

"CLR" wrote:

> Although awkward, you can append the Letters to the numbers in a cell with
> Custom Formatting........Right-click on the cell, choose FormatCells >
> NumberTab > Custom > and in the Type: window, put 0 A (that's zero and A, or
> whatever letter you wish to append) > OK.....then type only the number
> portion in the cell.
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Mango" wrote:
>
> > Hello-
> > I'm trying to sum columns that contain mostly number values, but that also
> > contain some occasional text. Excel, of course, ignores those cells that
> > contain text. I've noticed other users with similar questions, but I'm still
> > not getting it. The text does not have any quantitative value. It is merely a
> > visual tag that I want to print along with the number value. Is there a way
> > to make Excel ignore the text but still add the data? Perhaps a formula for
> > each cell that contains text + data that makes the SUM formula "see" only the
> > data for that cell? Please dumb it down for me, I'm new to Excel. Thanks.

Yes, try it on a test sheet...........
Where I put 0 A , you could put 0 (late fee) in for the formatting
and then just type the number in the cell.......

Vaya con Dios,
Chuck, CABGx3



"Mango" wrote:

> CLR-
> I'm sorry, I don't understand. Will Excel print the number + text in the
> cell, and add the number value in the SUM formula?
>
> "CLR" wrote:
>
> > Although awkward, you can append the Letters to the numbers in a cell with
> > Custom Formatting........Right-click on the cell, choose FormatCells >
> > NumberTab > Custom > and in the Type: window, put 0 A (that's zero and A, or
> > whatever letter you wish to append) > OK.....then type only the number
> > portion in the cell.
> >
> > Vaya con Dios,
> > Chuck, CABGx3
> >
> >
> >
> > "Mango" wrote:
> >
> > > Hello-
> > > I'm trying to sum columns that contain mostly number values, but that also
> > > contain some occasional text. Excel, of course, ignores those cells that
> > > contain text. I've noticed other users with similar questions, but I'm still
> > > not getting it. The text does not have any quantitative value. It is merely a
> > > visual tag that I want to print along with the number value. Is there a way
> > > to make Excel ignore the text but still add the data? Perhaps a formula for
> > > each cell that contains text + data that makes the SUM formula "see" only the
> > > data for that cell? Please dumb it down for me, I'm new to Excel. Thanks.

Hey Chuck- I tried doing what you said, but I got an error message saying
"Excel cannot use the number format you typed. Try using one of the built in
number formats." I typed in "0 +fee". Interestingly, I typed in "0+f" and it
worked! I then became curious and tried different scenarios. Typing "0f"," 0
f", "0(f)", "0 +f "worked. Typing "0(fee)", "0 fe", "0fee" brought up the
error message.I don't know what is going on.

"CLR" wrote:

> Yes, try it on a test sheet...........
> Where I put 0 A , you could put 0 (late fee) in for the formatting
> and then just type the number in the cell.......
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
> "Mango" wrote:
>
> > CLR-
> > I'm sorry, I don't understand. Will Excel print the number + text in the
> > cell, and add the number value in the SUM formula?
> >
> > "CLR" wrote:
> >
> > > Although awkward, you can append the Letters to the numbers in a cell with
> > > Custom Formatting........Right-click on the cell, choose FormatCells >
> > > NumberTab > Custom > and in the Type: window, put 0 A (that's zero and A, or
> > > whatever letter you wish to append) > OK.....then type only the number
> > > portion in the cell.
> > >
> > > Vaya con Dios,
> > > Chuck, CABGx3
> > >
> > >
> > >
> > > "Mango" wrote:
> > >
> > > > Hello-
> > > > I'm trying to sum columns that contain mostly number values, but that also
> > > > contain some occasional text. Excel, of course, ignores those cells that
> > > > contain text. I've noticed other users with similar questions, but I'm still
> > > > not getting it. The text does not have any quantitative value. It is merely a
> > > > visual tag that I want to print along with the number value. Is there a way
> > > > to make Excel ignore the text but still add the data? Perhaps a formula for
> > > > each cell that contains text + data that makes the SUM formula "see" only the
> > > > data for that cell? Please dumb it down for me, I'm new to Excel. Thanks.

Not quite sure if this is what you are after, but you might want to try:
=550+N("+late fee")
Only 550 will show up i the cell and you will be able to sum. =550+N("+late fee") will appear in the formula field.

Regards
Hans Knudsen



"Mango" <[email protected]> skrev i en meddelelse news:[email protected]...
> Hello-
> I'm trying to sum columns that contain mostly number values, but that also
> contain some occasional text. Excel, of course, ignores those cells that
> contain text. I've noticed other users with similar questions, but I'm still
> not getting it. The text does not have any quantitative value. It is merely a
> visual tag that I want to print along with the number value. Is there a way
> to make Excel ignore the text but still add the data? Perhaps a formula for
> each cell that contains text + data that makes the SUM formula "see" only the
> data for that cell? Please dumb it down for me, I'm new to Excel. Thanks.

Thanks Hans, but I want the text to show up in the cell.

"Hans Knudsen" wrote:

> Not quite sure if this is what you are after, but you might want to try:
> =550+N("+late fee")
> Only 550 will show up i the cell and you will be able to sum. =550+N("+late fee") will appear in the formula field.
>
> Regards
> Hans Knudsen
>
>
>
> "Mango" <[email protected]> skrev i en meddelelse news:[email protected]...
> > Hello-
> > I'm trying to sum columns that contain mostly number values, but that also
> > contain some occasional text. Excel, of course, ignores those cells that
> > contain text. I've noticed other users with similar questions, but I'm still
> > not getting it. The text does not have any quantitative value. It is merely a
> > visual tag that I want to print along with the number value. Is there a way
> > to make Excel ignore the text but still add the data? Perhaps a formula for
> > each cell that contains text + data that makes the SUM formula "see" only the
> > data for that cell? Please dumb it down for me, I'm new to Excel. Thanks.
>
>
>

Mango
Did you get your problem solved?
Otherwise you might want to try the custom format
0 "(late fee)"

Hans

"Mango" <[email protected]> skrev i en meddelelse news:[email protected]...
> Hey Chuck- I tried doing what you said, but I got an error message saying
> "Excel cannot use the number format you typed. Try using one of the built in
> number formats." I typed in "0 +fee". Interestingly, I typed in "0+f" and it
> worked! I then became curious and tried different scenarios. Typing "0f"," 0
> f", "0(f)", "0 +f "worked. Typing "0(fee)", "0 fe", "0fee" brought up the
> error message.I don't know what is going on.
>
> "CLR" wrote:
>
>> Yes, try it on a test sheet...........
>> Where I put 0 A , you could put 0 (late fee) in for the formatting
>> and then just type the number in the cell.......
>>
>> Vaya con Dios,
>> Chuck, CABGx3
>>
>>
>>
>> "Mango" wrote:
>>
>> > CLR-
>> > I'm sorry, I don't understand. Will Excel print the number + text in the
>> > cell, and add the number value in the SUM formula?
>> >
>> > "CLR" wrote:
>> >
>> > > Although awkward, you can append the Letters to the numbers in a cell with
>> > > Custom Formatting........Right-click on the cell, choose FormatCells >
>> > > NumberTab > Custom > and in the Type: window, put 0 A (that's zero and A, or
>> > > whatever letter you wish to append) > OK.....then type only the number
>> > > portion in the cell.
>> > >
>> > > Vaya con Dios,
>> > > Chuck, CABGx3
>> > >
>> > >
>> > >
>> > > "Mango" wrote:
>> > >
>> > > > Hello-
>> > > > I'm trying to sum columns that contain mostly number values, but that also
>> > > > contain some occasional text. Excel, of course, ignores those cells that
>> > > > contain text. I've noticed other users with similar questions, but I'm still
>> > > > not getting it. The text does not have any quantitative value. It is merely a
>> > > > visual tag that I want to print along with the number value. Is there a way
>> > > > to make Excel ignore the text but still add the data? Perhaps a formula for
>> > > > each cell that contains text + data that makes the SUM formula "see" only the
>> > > > data for that cell? Please dumb it down for me, I'm new to Excel. Thanks.

It would be helpful if you showed several examples of your data.


"Peo Sjoblom" <peo.sjoblom@nw^^excelsolutions.com> wrote in message
news:#Ro#[email protected]:

> Do you mean like
>
> 1
> 2
> a
> 3
> c
> etc
>
> then SUM(Range)
>
> will ignore the text values, but if your value are like
>
> 1a
> 2
> 3b
> etc
>
> then you can't sum unless you can extract the number but then the question
> is what are the different text
> strings, where in the cells are they (before/after number)?
>
> If the latter you should rethink your design and tag the values in separate
> cells, it's not a good design if you can't calculate numbers without first
> going to length in extracting them from text first
>
> Note that you can use a custom format in the cells like
>
> 0.00 "tag"
>
> then it will display as 12.25 tag
> but the value would be numerical
>
>
> --
>
> Regards,
>
> Peo Sjoblom
>
> http://nwexcelsolutions.com
>
>
> "Mango" <[email protected]> wrote in message
> news:[email protected]...
> > Hello-
> > I'm trying to sum columns that contain mostly number values, but that also
> > contain some occasional text. Excel, of course, ignores those cells that
> > contain text. I've noticed other users with similar questions, but I'm
> > still
> > not getting it. The text does not have any quantitative value. It is
> > merely a
> > visual tag that I want to print along with the number value. Is there a
> > way
> > to make Excel ignore the text but still add the data? Perhaps a formula
> > for
> > each cell that contains text + data that makes the SUM formula "see" only
> > the
> > data for that cell? Please dumb it down for me, I'm new to Excel. Thanks.