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 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

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.

"Max_power" <Max_power.254tlz_1143133201.6202@excelforum-nospam.com> wrote
in message news:Max_power.254tlz_1143133201.6202@excelforum-nospam.com...
>
> 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
>
>
> --
> Max_power
> ------------------------------------------------------------------------
> Max_power's Profile:
> http://www.excelforum.com/member.php...o&userid=32255
> View this thread: http://www.excelforum.com/showthread...hreadid=525763
>

[Ron Rosenfeld]

On Thu, 23 Mar 2006 10:55:47 -0600, Max_power
<Max_power.254tlz_1143133201.6202@excelforum-nospam.com> wrote:

>
>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

[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.



Max_power wrote:
>
> 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
>
> --
> Max_power
> ------------------------------------------------------------------------
> Max_power's Profile: http://www.excelforum.com/member.php...o&userid=32255
> View this thread: http://www.excelforum.com/showthread...hreadid=525763

--

Dave Peterson

[Ron Rosenfeld]

On Fri, 24 Mar 2006 03:11:30 -0600, Max_power
<Max_power.2562qy_1143191700.5878@excelforum-nospam.com> wrote:

>
>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]

On Fri, 24 Mar 2006 07:22:49 -0600, Max_power
<Max_power.256ebm_1143206700.567@excelforum-nospam.com> wrote:

>
>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]

On Fri, 24 Mar 2006 07:32:04 -0600, Max_power
<Max_power.256esg_1143207312.7406@excelforum-nospam.com> wrote:

>
>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]

On Fri, 24 Mar 2006 08:41:33 -0600, Max_power
<Max_power.256i0z_1143211501.7391@excelforum-nospam.com> wrote:

>
>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