Concatenate

[Max_power]

Hello,

I apologise for serial posting but I can now identify my problem.
I'd like to concatenate two columns of data. In the first column I hav
a lot of text, obviously there is some text that will be longer tha
others. In the second column I have a lot of numbers identifying th
text in the first column.
I'd like to allign the text and the numbers into one cell.
I know I can do this with the indent button on the toolbar but I have
lot of data and this would prove to be a tedious task if I were t
approach it like so.
Can anyone suggest any other methods.

Thank you very much in advance

Ma

 

[Guest]

Hi Max

What do you mean by 'align the text and the numbers into one cell"? Can you
post some plain text examples of what you have - and also what you would
like?

Cheers.
Andy.

 

[Ron Rosenfeld]

Hello,

I apologise for serial posting but I can now identify my problem.
I'd like to concatenate two columns of data. In the first column I have
a lot of text, obviously there is some text that will be longer than
others. In the second column I have a lot of numbers identifying the
text in the first column.
I'd like to allign the text and the numbers into one cell.
I know I can do this with the indent button on the toolbar but I have a
lot of data and this would prove to be a tedious task if I were to
approach it like so.
Can anyone suggest any other methods.

Thank you very much in advance

Max

What, exactly, do you mean by "allign" (sic)?

If you want everything right-aligned, you can just use the formula:

=A1&" "&B1

and right-align that column.

If you want the text left aligned, and the numbers right aligned, in the same
cell, you can do it with a formula.

HOWEVER, you will have to use a fixed pitch font and NOT a proportional font.
Something like Courier New will work:

=A1&TEXT(B1,REPT("_0",22-LEN(A1)-LEN(B1))&"0")

where the number "22" in the above is greater than the longest possible
combination of text string + number. Choose a number large enough so you have
a few spaces between the text and the number.


--ron

 

[Pete_UK]

Don't know why you have started a new thread - I've responded to your
earlier one with a solution to your problem.

Pete

 

[BB]

If you are trying to concatenate the two values into one cell, create a
third column ("C"), and use the Concatenate command to concatenate the
2 prevoius cells:

=CONCATENATE(A1,B1)

If you want a space between them, for example, do:

=CONCATENATE(A1," ",B1)

 

[Max_power]

Firstly many thanks who all took the time to reply to my query and
secondly I mean allign in the respect to the following.
I'm trying to concatenate two columns that will produce the following
results.
Column A says has product description and column B say has product id.
After I concatenate it, it will be something like

Sony walkman-213443
Hitachi tv-324234
Vanilla icecream-445345
king crisps-3424234
dell computer monitor-234324

I would like to have the following

Sony walkman---------213443
Hitachi tv-------------324234
Vanilla icecream-------445345
king crisps------------3424234
dell computer monitor--234324

Note though without the "-". As is quite apparant some text is longer
than others and I just want to have all the numbers right alligned in
the cell and all the text left alligned in the cell. I have a lot of
data to go though so clicking the indent button on the toolbar is quite
tedious and it would take me until next christmas to complete the task!

Can this be done through a formula?

Many many thanks for any help

Max

 

[Dave Peterson]

I put the data in A1:B100
and used a formula like this in D1:
=A1&RIGHT(REPT(" ",50)&B1,50-LEN(A1))
(With a courier new font to make thinks line up)

But this also worked (with a single space between the two values):

=LEFT(A1&REPT(" ",MAX(LEN($A$1:$A$100))),MAX(LEN($A$1:$A$100)))
&RIGHT(REPT(" ",MAX(LEN($B$1:$B$100)))&B1,MAX(LEN($B$1:$B$100))+1)

(all one cell)

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.

 

[Ron Rosenfeld]

Firstly many thanks who all took the time to reply to my query and
secondly I mean allign in the respect to the following.
I'm trying to concatenate two columns that will produce the following
results.
Column A says has product description and column B say has product id.
After I concatenate it, it will be something like

Sony walkman-213443
Hitachi tv-324234
Vanilla icecream-445345
king crisps-3424234
dell computer monitor-234324

I would like to have the following

Sony walkman---------213443
Hitachi tv-------------324234
Vanilla icecream-------445345
king crisps------------3424234
dell computer monitor--234324

Note though without the "-". As is quite apparant some text is longer
than others and I just want to have all the numbers right alligned in
the cell and all the text left alligned in the cell. I have a lot of
data to go though so clicking the indent button on the toolbar is quite
tedious and it would take me until next christmas to complete the task!

Can this be done through a formula?

What happened when you used the method I posted???

--ron

 

[Max_power]

Hi, many thanks for the reply. I got a "value" error.
Any suggestions?

 

[Max_power]

I apologise, my last post was incorrect. What happened was it alligned
the text correctly but it just duplicated my first column where my
second column should have been.

 

[Max_power]

I apologise, my last post was incorrect. What happened was it alligned
the text correctly but it just duplicated my first column where my
second column should have been.

 

[Ron Rosenfeld]

Hi, many thanks for the reply. I got a "value" error.
Any suggestions?

My guess as to the most likely reason is that you didn't alter the "22" in the
original formula. Let me repost what I wrote and ask you to pay special
attention to the first line of text instructions after the formula.

=====================
=A1&TEXT(B1,REPT("_0",22-LEN(A1)-LEN(B1))&"0")

where the number "22" in the above is greater than the longest possible
combination of text string + number. Choose a number large enough so you have
a few spaces between the text and the number.
=====================

If that is not the problem, then post back with the exact data that is causing
the problem, and the exact formula as you modified it for your usage.

-------------------------------------

Also, if you missed that, you may also have missed the requirement to use a
FIXED-PITCH FONT (like Courier New).

===================================
HOWEVER, you will have to use a fixed pitch font and NOT a proportional font.
Something like Courier New will work
===================================

--ron

 

[Ron Rosenfeld]

I apologise, my last post was incorrect. What happened was it alligned
the text correctly but it just duplicated my first column where my
second column should have been.

Post back with the exact formula you are using, and the exact contents of A1
and B1. I suspect you transcribed the formula incorrectly.
--ron

 

[Max_power]

I'd like to thank you so much for your assistance. I can't post the
contents of my xls sheet for security reasons. The formula kind of
worked it doesn't allign them correctly as there are still some small
errors but it's still a hell of a lot better than I had done myself.
It's good excel experts like you post on these forums to help newbies
like myself

thanks again

Max

 

[Max_power]

I'm sorry I see where it is now that I'm going wrong. Two of the columns
that I'm concatenating are text columns. The first formula works perfect
with one text and one integer. Can I make excel read one of the text
columns as an integer?

Thanks

 

[Ron Rosenfeld]

I'm sorry I see where it is now that I'm going wrong. Two of the columns
that I'm concatenating are text columns. The first formula works perfect
with one text and one integer. Can I make excel read one of the text
columns as an integer?

Thanks

On my worksheet, the formula works whether the second column is a number
formatted as a number, or a number formatted as text.

Try this formula instead:

=CONCATENATE(A1,REPT(" ",22-LEN(A1)-LEN(B1)),B1)

1. Change the 22 to whatever is necessary so that it is greater than the
maximum sum of the lengths of strings in A1 & B1).

2. USE a fixed pitch font (e.g. Courier, or Courier New, or Lucida Sans
Typewriter or ...


--ron