[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel summing up text

Posted on 2014-07-16
14
Medium Priority
?
157 Views
Last Modified: 2014-07-23
Hello,
I have created the first three columns with =TEXT(MONTH($B1),"00"), =TEXT(DAY($B1),"00"), and  =TEXT(YEAR($B1),"00")
07      17      2014      0717
The fourth column is simply combining the text; =$B$1&$C$1
I have several of these.  Now I need to add them all up.  How do i add "text"?  The text are numbers.
For example:
0717
0718
0719
etc.

Thanks
0
Comment
Question by:chima
14 Comments
 
LVL 2

Expert Comment

by:HAJ2014
ID: 40200029
=sum(b1:b5)  

It will add everything in between those cells  b1,b2,b3,b4,b5.



Thanks,

Henry
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40200035
=VALUE($B$1)+VALUE($C$1)
0
 

Author Comment

by:chima
ID: 40200037
HA2014 (short 14), yeah one would link so, this is the first thing I tried, but it did not work.  Did you try it?
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:chima
ID: 40200042
Randy, you might be on the right path; =sum(VALUE($B$1:$B$5)  I'll go try it.
0
 

Author Comment

by:chima
ID: 40200049
Just in case Randy got it so quickly, may i ask another question with this one?  How can I "associate" two or more tabs within the same "worksheet," so that if one tab is move the other will follow?  Or, make two or more tabs look/act as one tab?  In other words; "joining tabs"?
0
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40200063
Elaborate on tabs, do you mean 2 worksheets in the same workbook?
0
 

Author Comment

by:chima
ID: 40200142
Randy, Oops!, you are right 2 worksheets in the same workbook?

In answer to adding text, again you are right.  I had to add VALUE() to each text cell, then do the summation.
This works, but for my purpose it drops the 0 between two numbers;
=TEXT(MONTH($B1),"00") has 07
=TEXT(DAY($B1),"00") has 17
and  =TEXT(YEAR($B1),"00") has 2014
07      17      2014    

The fourth column is simply combining the text; =$B$1&$C$1 has  0717
When I use VALUE() it convert it to 717, which this is okay, but when i get to August, 0801 is converted to 81 and I need 801.

 May I ask the question this way.  I have a second sheet/tab and I use this ='62 Max Days'!E1
E1 has this =$B$1&$C$1  
E1 is in the first worksheet,  so this is okay =VALUE($B$1)&VALUE($C$1), but the zeroes are dropped.
And I can't do this; =VALUE('62 Max Days'!E1)  nor can I do this ='62 Max Days'!VALUE(E1)
And this does not work; ='62 Max Days'!(VALUE(E1))

Comments?
0
 

Author Comment

by:chima
ID: 40200154
Excel-tab-association-tie.PNGThese are the two tabs I want to associate.  In the first sheet I have my data and in the second I do some calculations.
I cannot combine the two, because (to my way of thinking) on the first sheet I can click on the upper left button to highlight ALL of the rows and columns.  If I had calculations there, I would grab those, which I do not wish to do so.
0
 
LVL 21

Accepted Solution

by:
Randy Poole earned 1400 total points
ID: 40200231
Not sure on the association of 2 sheets, never heard that before.  As for the value you would do a value of
=VALUE($B$1&$C$1) which would omit the leading zero but should work fine on the 08 example you posted.
0
 

Author Comment

by:chima
ID: 40200876
Randy, one would hope.  Tried it and this is what I get;
Each cell is a separate value and drops the leading zero
0
 
LVL 34

Assisted Solution

by:Rob Henson
Rob Henson earned 600 total points
ID: 40204277
To overcome adding up the text values, assuming your B1&C1 formulas are in column D, use:

=SUM(D1:D5*1)  Confirm with Shift Ctrl & Enter, this will add {   } at either end of the formula. This will convert 0717 to 717 and 0801 to 801.

This will do the same as Randy's suggestion:

=SUM(VALUE($B$1:$B$5))

but will also need entering with Shift Ctrl & Enter.

On a broader note, what are you trying to acheive by adding up your date values? If you want to determine number of days between two dates you can just subtract earlier date from later date and then format as a number rather than a date.

Thanks
Rob H
0
 

Author Comment

by:chima
ID: 40211850
Rob and Randy, I apologize for the delay...off on a long weekend.  Rob I will try your suggestion and get back to you.
I'm testing this application which manages days and the data for each day.  I am simply associating a date with data indicating which date it is.  See you before the morning is over.
0
 

Author Comment

by:chima
ID: 40212007
Well, I need to apologize to Randy.  When I tired VALUE($B$1&$B$5) I did not see that it actually worked.  I must have done something wrong.  Problem solved, I'll issue the points shortly.
0
 

Author Closing Comment

by:chima
ID: 40214623
Thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question