| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Create a simple average that does NOT include blanks cells | Excel Worksheet Functions | |||
| Using concatenate to include a line return | Excel Discussion (Misc queries) | |||
| Concatenate and remove blanks | Excel Worksheet Functions | |||
| Include date in concatenate statement | Excel Discussion (Misc queries) | |||
| Trailing blanks | Excel Discussion (Misc queries) | |||
Hybrid Mode
