 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / July 2007Custom Fields with spaces
Hi, The easiest way to explain my dilemma is by example. I need a custom field that is always thirty character spaces long no matter how long the text is. So for example, lets say the word is PIZZA which is 5 characters, I would need 25 blank spaces afterwards to get to 30 characters in total. Does anybody know what this would look like? I've tried to use the _ but that doesn't seem to work. Thanks. one method if your text is in A1 =a1&Rept(" ",30-len(A1)) > Hi, > The easiest way to explain my dilemma is by example. I need a custom [quoted text clipped - 3 lines] > characters in total. Does anybody know what this would look like? > I've tried to use the _ but that doesn't seem to work. Thanks. > one method > if your text is in A1 [quoted text clipped - 10 lines] > > - Show quoted text - Perfect! Thanks~ :) Plum-cool BJ, Plum-cool........... Vaya con Dios, Chuck, CABGx3 > one method > if your text is in A1 [quoted text clipped - 7 lines] > > characters in total. Does anybody know what this would look like? > > I've tried to use the _ but that doesn't seem to work. Thanks. Suppose A1 has the text you want to format, then use this formula in B1: =A1&REPT(CHAR(32),30-LEN(A1)) > Hi, > The easiest way to explain my dilemma is by example. I need a custom [quoted text clipped - 3 lines] > characters in total. Does anybody know what this would look like? > I've tried to use the _ but that doesn't seem to work. Thanks. One more: With Pizza in A1: =left(a1&rept(" ",30),30) > Hi, > The easiest way to explain my dilemma is by example. I need a custom [quoted text clipped - 3 lines] > characters in total. Does anybody know what this would look like? > I've tried to use the _ but that doesn't seem to work. Thanks.  SignatureDave Peterson > The easiest way to explain my dilemma is by example. I need a custom > field that is always thirty character spaces long no matter how long > the text is. So for example, lets say the word is PIZZA which is 5 > characters, I would need 25 blank spaces afterwards to get to 30 > characters in total. Does anybody know what this would look like? > I've tried to use the _ but that doesn't seem to work. Thanks. What did you want to happen if the user typed in more than 30 characters? The formulas posted by 'bj' and 'a7n9' will error out with a #VALUE error; the formula posted by Dave will truncate the entry at the 30th character; and the following formula will preserve all characters (thus returning a value longer than 30 characters for a long entry). =REPLACE(REPT(" ",30),1,LEN(A1),A1) Rick Good point, but in this case, it looks like the OP actually wants it truncated--well if "always thirty character spaces long no matter how long the text is" actually means that. > > The easiest way to explain my dilemma is by example. I need a custom > > field that is always thirty character spaces long no matter how long [quoted text clipped - 12 lines] > > Rick SignatureDave Peterson > Good point, but in this case, it looks like the OP actually wants it > truncated--well if "always thirty character spaces long no matter > how long the text is" actually means that. Oh, I agree. I just wanted to make sure the OP was alerted to the different results the methods produced for over 30 characters entered. That way, if the possibility of this situation never occurred to the OP, he/she would have a choice of how to account for it. Rick I never remember =replace(). I would have used an =if(len()) approach. I guess I was just being argumentative on a Saturday morning <bg>. http://wordnet.princeton.edu/perl/webwn?s=curmudgeon > > Good point, but in this case, it looks like the OP actually wants it > > truncated--well if "always thirty character spaces long no matter [quoted text clipped - 6 lines] > > Rick SignatureDave Peterson "Rick Rothstein (MVP - VB)" <rickNOSPAMnews@NOSPAMcomcast.net> wrote... ... >=REPLACE(REPT(" ",30),1,LEN(A1),A1) Wouldn't =LEFT(A1&REPT(" ",30),30) be simpler? >>=REPLACE(REPT(" ",30),1,LEN(A1),A1) > > Wouldn't > > =LEFT(A1&REPT(" ",30),30) Well, that is the same formula that Dave posted... I posted my formula in order to provide the OP with a choice in case the user enters more than 30 characters. Your (and Dave's) formula will truncate the entry at 30 characters for entries with more than 30 characters in them (which, I freely admit, is more than likely what the OP wants for this case) whereas the formula I posted preserves all of the character typed in (just in case the OP wants to be able to do that). Rick "Rick Rothstein (MVP - VB)" <rickNOSPAMnews@NOSPAMcomcast.net> wrote... >>>=REPLACE(REPT(" ",30),1,LEN(A1),A1) >> >> Wouldn't >> >> =LEFT(A1&REPT(" ",30),30) ... >Well, that is the same formula that Dave posted... I posted my formula in >order to provide the OP with a choice in case the user enters more than 30 [quoted text clipped - 3 lines] >the formula I posted preserves all of the character typed in (just in case >the OP wants to be able to do that). Didn't see Dave's response. So how 'bout =A1&REPT(" ",MAX(0,30-LEN(A1))) ? >>>>=REPLACE(REPT(" ",30),1,LEN(A1),A1) >>> [quoted text clipped - 13 lines] > > =A1&REPT(" ",MAX(0,30-LEN(A1))) Okay, that is another way to do it. My guess is that if we keep trying, there are probably a few other ways to accomplish this. Are you proposing that your formula is the better one to use for some reason (perhaps, it's more efficient or faster)? Rick "Rick Rothstein (MVP - VB)" <rickNOSPAMnews@NOSPAMcomcast.net> wrote... >>>>>=REPLACE(REPT(" ",30),1,LEN(A1),A1) ... >>=A1&REPT(" ",MAX(0,30-LEN(A1))) > >Okay, that is another way to do it. My guess is that if we keep trying, >there are probably a few other ways to accomplish this. Are you proposing >that your formula is the better one to use for some reason (perhaps, it's >more efficient or faster)? It's shorter. It may be more efficient, depending on how REPLACE works vs concatenation. >>>>>>=REPLACE(REPT(" ",30),1,LEN(A1),A1) > ... [quoted text clipped - 8 lines] > > It may be more efficient, depending on how REPLACE works vs concatenation. Shorter... OK, I can buy that. Rick
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.