Combine two cells without losing data cell formats

J

johncaleb

I need a macro, or a function to combine 2 columns of data into one column
WITHOUT losing the text-justification of each. See below.

what i have: 1st column is left-justified text. 2nd column is
right-justified text.

[john ][ smith]
[sam ][ williams]
[antwane][ carlson]

what i need in each cell, which is no larger than a cell width of 88.14 or
622pixels :

[john smith]
[sam williams]
[antwane carlson]


if i concatenate, i get the below which is not what i need:
[john smith ]
[sam williams ]
[antwane carlson ]


can i put another formula in to justify all last names to the right in one
cell? maybe take the difference between the number of text that can fit into
cell width of 88.14, and the total amount of text from both cells and space
that amount in between the result?? I don't really know...maybe overthinking
it.

Please help
thx!!
 
R

Rick Rothstein

If you concatenate with a formula like this...

=A1&" "&B1

then you can Format the Cell's Horizontal Text Alignment to "Distributed" to
get what I think you are describing.
 
F

FSt1

hi
didn't like the answers you got on 5/3, huh.
oh well.
not possible. a cell cannot be both left justified AND right justified.
but if you want, try this.
in C2, concatinate cells A2 and B2. copy and paste special values. adjust
the cell width to your liking. add spaces in between the names to your
lilking. then in the cell below enter this formula.
=A3&REPT(" ",LEN(C2)-(LEN(A3)+LEN(B3)))&B3
copy down.
the above formula uses C2 as a standard number of cells to add spaces to the
cell below. but it probably wont be exact widths. where as spaces will all be
the same width, other characters are not. compare a W to an I. so the results
will be an approximate to what you want.
when done, copy the whole column and paste special values.

Regards
FS1t
 
F

FSt1

afterthought,
you might get around the varing character width by changing to a different
font.
try font courier.

Regards
FSt1

FSt1 said:
hi
didn't like the answers you got on 5/3, huh.
oh well.
not possible. a cell cannot be both left justified AND right justified.
but if you want, try this.
in C2, concatinate cells A2 and B2. copy and paste special values. adjust
the cell width to your liking. add spaces in between the names to your
lilking. then in the cell below enter this formula.
=A3&REPT(" ",LEN(C2)-(LEN(A3)+LEN(B3)))&B3
copy down.
the above formula uses C2 as a standard number of cells to add spaces to the
cell below. but it probably wont be exact widths. where as spaces will all be
the same width, other characters are not. compare a W to an I. so the results
will be an approximate to what you want.
when done, copy the whole column and paste special values.

Regards
FS1t

johncaleb said:
I need a macro, or a function to combine 2 columns of data into one column
WITHOUT losing the text-justification of each. See below.

what i have: 1st column is left-justified text. 2nd column is
right-justified text.

[john ][ smith]
[sam ][ williams]
[antwane][ carlson]

what i need in each cell, which is no larger than a cell width of 88.14 or
622pixels :

[john smith]
[sam williams]
[antwane carlson]


if i concatenate, i get the below which is not what i need:
[john smith ]
[sam williams ]
[antwane carlson ]


can i put another formula in to justify all last names to the right in one
cell? maybe take the difference between the number of text that can fit into
cell width of 88.14, and the total amount of text from both cells and space
that amount in between the result?? I don't really know...maybe overthinking
it.

Please help
thx!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top