»
Tech Talk
»
Beyond the Basics
»
MS Excel
| Beyond the Basics |
|
MS Excel |
|
|
Christopher
Moderator
Member Rara Avis
since 1999-08-02
Posts 8296Purgatorial Incarceration 
|
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 |
||
| © Copyright 2002 C.G. Ward - All Rights Reserved | |||
|
Ron
Administrator
Member Rara Avis
since 1999-05-19
Posts 8669Michigan, US |
You, uh, concatenate them, too, same as with fields.  = A1 & " " & B1 |
||
|
Christopher
Moderator
Member Rara Avis
since 1999-08-02
Posts 8296Purgatorial Incarceration |
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: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 1999-05-19
Posts 8669Michigan, US |
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 1999-08-02
Posts 8296Purgatorial Incarceration |
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 1999-08-02
Posts 8296Purgatorial Incarceration |
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 1999-05-19
Posts 8669Michigan, US |
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 1999-08-02
Posts 8296Purgatorial Incarceration |
EXCELLENT! That will work perfectly. Thank you very much sir (it will clean up the code too) C |
||
|
Christopher
Moderator
Member Rara Avis
since 1999-08-02
Posts 8296Purgatorial Incarceration |
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).] |
||
|
|
⇧ top of page ⇧ |
|
| All times are ET (US). All dates are in Year-Month-Day format. | ||
