Concatenate and include trailing blanks

  • Thread starter Thread starter Mr_Wilf
  • Start date Start date
M

Mr_Wilf

Hi,

I need to concatenate three seperate Text fields in to one long string, but
keeping each value to 30 char.

eg
A1 "999 Letsbe Avenue"
B1 "Little Doing Quickly"
C1 "Abyssinia"

into "999 Letsbe Avenue Little Doing Quickly Abyssinia
"
instead of "999 Letsbe AvenueLittle Doing QuicklyAbyssinia" which is what
I'm getting.

Any suggestions?
 
Hi

=A1&REPT(" ",30-LEN(A1))&B1&REPT(" " ,30-LEN(B1))&C1&REPR(" ",30-LEN(C1))

--
Regards
Roger Govier

Mr_Wilf said:
Hi,

I need to concatenate three seperate Text fields in to one long string,
but
keeping each value to 30 char.

eg
A1 "999 Letsbe Avenue"
B1 "Little Doing Quickly"
C1 "Abyssinia"

into "999 Letsbe Avenue Little Doing Quickly
Abyssinia
"
instead of "999 Letsbe AvenueLittle Doing QuicklyAbyssinia" which is what
I'm getting.

Any suggestions?

__________ Information from ESET Smart Security, version of virus
signature database 4533 (20091022) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4533 (20091022) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Sorry, typo in previous post

last REPR should have been REPT

=A1&REPT(" ",30-LEN(A1))&B1&REPT(" " ,30-LEN(B1))&C1&REPT(" ",30-LEN(C1))

--
Regards
Roger Govier

Roger Govier said:
Hi

=A1&REPT(" ",30-LEN(A1))&B1&REPT(" " ,30-LEN(B1))&C1&REPR(" ",30-LEN(C1))

--
Regards
Roger Govier



__________ Information from ESET Smart Security, version of virus
signature database 4533 (20091022) __________

The message was checked by ESET Smart Security.

http://www.eset.com
Click to expand...

__________ Information from ESET Smart Security, version of virus signature database 4533 (20091022) __________

The message was checked by ESET Smart Security.

http://www.eset.com
 
Another way:
=left(a1&rept(" ",30),30) & left(b1&rept(" ",30),30) & left(c1&rept(" ",30),30)

If you ever have numbers that need to be formatted nicely (dates, times,
money??), you could use:

=left(text(a1,"$#,##0.00")&rept(" ",30),30) & ...
 
Looking good - many thanks

Roger Govier said:
Sorry, typo in previous post

last REPR should have been REPT

=A1&REPT(" ",30-LEN(A1))&B1&REPT(" " ,30-LEN(B1))&C1&REPT(" ",30-LEN(C1))

--
Regards
Roger Govier



__________ Information from ESET Smart Security, version of virus signature database 4533 (20091022) __________

The message was checked by ESET Smart Security.

http://www.eset.com



.
Click to expand...
 
Back
Top