How to Join Member's Area Private Library Search Today's Topics p Login
Main Forums Discussion Tech Talk Mature Content Archives
   Nav Win
 Tech Talk
 Beyond the Basics
 MS Excel
 1 2 3 4 5 6 7 8
Follow us on Facebook

 Moderated by: Ron   (Admins )

 
User Options
Format for Better Printing EMail to a Friend Not Available
Admin Print Send ECard
Passions in Poetry

MS Excel

 Post A Reply Post New Topic   Go to the Next Oldest/Previous Topic Return to Topic Page Go to the Next Newest Topic 
Christopher
Moderator
Member Rara Avis
since 08-02-99
Posts 9130
Purgatorial Incarceration


0 posted 04-01-2002 02:26 PM       View Profile for Christopher   Email Christopher   Edit/Delete Message      Find Poems  View IP for Christopher


Ok - Not sure this is the right forum for it... but i can try.

How do you add a space in between items in Excel when concatenating them? I'm lost here... i know "Trim" removes all but one space between things... uhmm... but i can't figure out how to add them. I know it's probably a silly question, but when concatenating text, it looks awfully funny with no separator.

Thanks

Chris
Ron
Administrator
Member Rara Avis
since 05-19-99
Posts 9708
Michigan, US


1 posted 04-01-2002 06:14 PM       View Profile for Ron   Email Ron   Edit/Delete Message      Find Poems   Click to visit Ron's Home Page   View IP for Ron

You, uh, concatenate them, too, same as with fields.  

= A1 & " " & B1
Christopher
Moderator
Member Rara Avis
since 08-02-99
Posts 9130
Purgatorial Incarceration


2 posted 04-01-2002 06:27 PM       View Profile for Christopher   Email Christopher   Edit/Delete Message      Find Poems  View IP for Christopher

heh

yeah, i knew that part, but re-reading what i wrote, that's the most logical answer i can expect with as little as i gave...

i'm afraid i wasn't quite as wordy as i should have been... i've been doing some of the more 'complicated' stuff in excel, and think, that probably i need to wait until i know a little more VB before attacking this part... but what the heck. my code for a particular cell looks as follows.
quote:
=IF('Week 1'!E9,CONCATENATE(IF('Week 1'!D9,"R",""),IF('Week 1'!E3,CONCATENATE(", ",'Week 1'!F3),""),IF('Week 1'!E4,CONCATENATE(", ",'Week 1'!F4),""),IF('Week 1'!E5,CONCATENATE(", ",'Week 1'!F5),""),IF('Week 1'!E6,CONCATENATE(", ",'Week 1'!F6),""),IF('Week 1'!E7,CONCATENATE(", ",'Week 1'!F7),""),IF('Week 1'!E8,CONCATENATE(", ",'Week 1'!F8),"")),"R")
the hyperlink for the sheet is http://countlesshorizons.com/temp/timesheet.xls

if you enter some numbers in a couple of places in column E, then select the "type" of time it is in column F, then go to the "Totals" worksheet and look at "C3" and you'll see how it looks when i do that... maybe it's just a logic string i'm not following somewhere, but if you add regular hours (Week 1, Column "D") the concatenation looks fine. I was hoping there was some function to add a space if it was called for.

Thank you for you help

C
Ron
Administrator
Member Rara Avis
since 05-19-99
Posts 9708
Michigan, US


3 posted 04-01-2002 07:44 PM       View Profile for Ron   Email Ron   Edit/Delete Message      Find Poems   Click to visit Ron's Home Page   View IP for Ron

I must be missing something.

I put in a few numbers, set the type for each and came up with "R, H, O/T" in Totals!C3. Your formula is adding both a comma and a space between each type. Where else did you want a space character?
Christopher
Moderator
Member Rara Avis
since 08-02-99
Posts 9130
Purgatorial Incarceration


4 posted 04-01-2002 08:00 PM       View Profile for Christopher   Email Christopher   Edit/Delete Message      Find Poems  View IP for Christopher

actually Ron, thank you very much, the problem i am having is when you DON'T put a number in the "regular" time. then you end up with " ,O/T, H" etc...

Christopher
Moderator
Member Rara Avis
since 08-02-99
Posts 9130
Purgatorial Incarceration


5 posted 04-01-2002 08:02 PM       View Profile for Christopher   Email Christopher   Edit/Delete Message      Find Poems  View IP for Christopher

i should expand - like i was saying, i didn't put it across very well (happens a lot, lol) i was hoping that there was an "automatic" way to include a space, aside from the concatenation, so that it would only include it IF it was necessary.
Ron
Administrator
Member Rara Avis
since 05-19-99
Posts 9708
Michigan, US


6 posted 04-01-2002 08:32 PM       View Profile for Ron   Email Ron   Edit/Delete Message      Find Poems   Click to visit Ron's Home Page   View IP for Ron

Got'cha. There is no automatic way to add separators. To do what you want would require yet another nested IF to avoid adding the first comma space set. Nasty.

If it was me? Since your codes are short and don't contain spaces, and especially since width can be a factor when you concatenate more than three codes, I would simply not use the comma character. Separate each code with a single space char, then put your whole formula inside the TRIM() function (which will eliminate any leading spaces, too).
Christopher
Moderator
Member Rara Avis
since 08-02-99
Posts 9130
Purgatorial Incarceration


7 posted 04-02-2002 10:19 AM       View Profile for Christopher   Email Christopher   Edit/Delete Message      Find Poems  View IP for Christopher

EXCELLENT!

That will work perfectly. Thank you very much sir (it will clean up the code too)

C
Christopher
Moderator
Member Rara Avis
since 08-02-99
Posts 9130
Purgatorial Incarceration


8 posted 04-02-2002 12:14 PM       View Profile for Christopher   Email Christopher   Edit/Delete Message      Find Poems  View IP for Christopher

ok well it didn't clean it as much as i thought at first, but it works!  

and, it gave me some ideas to maybe clean it up as a whole... i love learning new things!

hey Ron - how would you go about editing the attributes of a nested block in autocad?

just hoping to ask a question that you don't know

[This message has been edited by Christopher (04-02-2002 12:14 PM).]

 
 Post A Reply Post New Topic   Go to the Next Oldest/Previous Topic Return to Topic Page Go to the Next Newest Topic 
All times are ET (US) Top
  User Options
>> Tech Talk >> Beyond the Basics >> MS Excel Format for Better Printing EMail to a Friend Not Available
Print Send ECard

 

pipTalk Home Page | Main Poetry Forums

How to Join | Member's Area / Help | Private Library | Search | Contact Us | Today's Topics | Login
Discussion | Tech Talk | Archives | Sanctuary



© Passions in Poetry and netpoets.com 1998-2013
All Poetry and Prose is copyrighted by the individual authors