Custom Fields with spaces

  • Thread starter Thread starter durstbj
  • Start date Start date
D

durstbj

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.
 
Suppose A1 has the text you want to format, then use this formula in B1:
=A1&REPT(CHAR(32),30-LEN(A1))
 
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.
Click to expand...

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.
 
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.
Click to expand...

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
 
=REPLACE(REPT(" ",30),1,LEN(A1),A1)
Wouldn't

=LEFT(A1&REPT(" ",30),30)
Click to expand...

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) said:
....
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).
Click to expand...

Didn't see Dave's response. So how 'bout

=A1&REPT(" ",MAX(0,30-LEN(A1)))

?
 
=REPLACE(REPT(" ",30),1,LEN(A1),A1)
Didn't see Dave's response. So how 'bout

=A1&REPT(" ",MAX(0,30-LEN(A1)))
Click to expand...

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) said:
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)?
Click to expand...

It's shorter.

It may be more efficient, depending on how REPLACE works vs concatenation.
 
=REPLACE(REPT(" ",30),1,LEN(A1),A1)
...

It's shorter.

It may be more efficient, depending on how REPLACE works vs concatenation.
Click to expand...

Shorter... OK, I can buy that.

Rick
 
Back
Top