Concatenating text preserving spaces vba

QuestionEdit

QUESTION: ,

I'm trying to bring all my formatted cells from B-N)into column A. I need to maintain the exact layout of the data, and it works for most cells, except where varied lengths of text are involved. (some names are 18 char, some are 22 etc.) For example, the cell containing a name (last, first MI) should be 30 characters long, and it looks 30 characters long (spaces fill to the end) until I concatenate it.

   Columns("E:E").Select 
   Selection.ColumnWidth = 30 

I say "concatenate", but I'm actually using the following formula:

   Sheets("New01").Select
   Columns("A:A").Select
   Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
   Range("A1").Select
   ActiveCell.FormulaR1C1 = "=RC[1]&RC[2]&RC[3]&RC[4]&RC[5]&RC[6]&RC[7]&RC[8]&RC[9]&RC[10]&RC[11]&RC[12]&RC[13]"
   Range("A1").Select
   Selection.Copy
   Range("C1").End(xlDown).Offset(0, -2).Select
   Range(Selection, Selection.End(xlUp)).Select
   ActiveSheet.Paste

Do you know how I can concatenate the text and keep the trailing spaces, so the next column of data lines up after 30 characters/spaces?

Alison

ANSWER: Alison,

If you want to look at it and have the characters line up, you need to make sure you are using a Mono-spaced font like courier new.

If I wanted to concatenate B1 and C1 so that B1 is 30 characters and C1 is 10 characters I would do this

for left justified

=LEFT(B1&REPT(" ",30),30)&LEFT(C1&REPT(" ",10),10)

>how I can concatenate the text and keep the trailing spaces

your formula would not remove trailing spaces - so maybe the cell doesn't have trailing spaces. My formula adds the necessary spaces or turncates the text if it is too big.

---------- FOLLOW-UP ----------

QUESTION: In order to get this to work, I had to add another set of "" within the brackets. ActiveCell.Formula = "=LEFT(F1%26REPT("" "",30),30)%26LEFT(I1%26REPT("" "",13),13)" Although when I look in the spreadsheet after it has run, it just shows: =LEFT(F1%26REPT(" ",30),30)%26LEFT(I1%26REPT(" ",13),13)

So, if I am specifying the length as I concatenate, do I still need: Columns("A:A").Select

   Selection.ColumnWidth = 6
   Columns("B:B").Select
   Selection.ColumnWidth = 2
   Columns("C:C").Select
   Selection.ColumnWidth = 9
   Columns("D:D").Select
   Selection.ColumnWidth = 2
   Columns("E:E").Select
   Selection.ColumnWidth = 30
   Columns("F:F").Select
   Selection.ColumnWidth = 1
   Columns("G:G").Select
   Selection.ColumnWidth = 1
   Columns("H:H").Select
   Selection.ColumnWidth = 13
   Columns("I:I").Select
   Selection.ColumnWidth = 5
   Columns("J:J").Select
   Selection.ColumnWidth = 1

As I'm working on this, it certainly makes it easier to see the content, so I may leave it in there.

-)

Alison

AnswerEdit

Alison,

What I gave you was a worksheet formula. When you apply that formula using VBA, any embedded double quotes do need to be doubled. I assumed you were aware of this - but sorry for not mentioning it.

activecell.formula = "=LEFT(B1&REPT("" "",30),30)&LEFT(C1&REPT("" "",10),10)"

would produce the original

=LEFT(B1&REPT(" ",30),30)&LEFT(C1&REPT(" ",10),10) in a cell.

Setting the column width in the other columns does nothing for the content of the cell. It does not add spaces to the content. It is just how it appears when you look at it. Now if you are going to do a save as a .prn file (fixed width) with multiple columns of data, then the column width does have an effect. However, I assumed your purpose in putting the formula in column A was to get your fixed width data arrayed all in one column and you would eventually replace the formulas in column A with the values they return using a paste special values and then delete all the other columns and do a save as to a text file.

So those were some assumptions I was working under - I don't know why - either from some earlier post that a vaguely remember seeing which may or may not have been from you and I don't know if it was addressed to me or someone else. In any event, whether you retain the code to do the spacing on the other columns, would depdnd on what the purpose was and I don't really know what that purpose is.