Concatenate and include trailing blanks
 

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?

Concatenate and include trailing blanks
 

Hi

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

--
Regards
Roger Govier

"Mr_Wilf" wrote in message
...
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




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

The message was checked by ESET Smart Security.

http://www.eset.com

Concatenate and include trailing blanks
 

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" <roger@technology4unospamdotcodotuk wrote in message
...
Hi

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

--
Regards
Roger Govier

"Mr_Wilf" wrote in message
...
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




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

The message was checked by ESET Smart Security.

http://www.eset.com




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

The message was checked by ESET Smart Security.

http://www.eset.com

Concatenate and include trailing blanks
 

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) & ...



Mr_Wilf wrote:

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?

--

Dave Peterson

Concatenate and include trailing blanks
 

Looking good - many thanks

"Roger Govier" wrote:

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" <roger@technology4unospamdotcodotuk wrote in message
...
Hi

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

--
Regards
Roger Govier

"Mr_Wilf" wrote in message
...
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




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

The message was checked by ESET Smart Security.

http://www.eset.com




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

The message was checked by ESET Smart Security.

http://www.eset.com



.

Concatenate and include trailing blanks
 

Thanks for answering - have gone with Roger's technique but logged yours in
my notes

"Dave Peterson" wrote:

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) & ...



Mr_Wilf wrote:

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?

--

Dave Peterson
.