Prev: Linking text cells between 2 worksheets
Next: Create new records from existing ones based on 'rule'?
From: Derek M on 27 May 2010 17:48
Hi all

I would like to do this in excel and wonder if its possible

In cell a1 i have 1234 in b2 i have an address in c3 i have 600.00 and in d4
i have 1

I have to have them concatenated but also i need 5 spaces after the first
cell, 4 spaces after the second and 6 spaces after the third when
concatenated (hope this makes sense)

So it would look like this in a1 eventually:

1234 47 Beufort drive 600.00 1

Hope someone might be abke to help as i have 685 lines of this to do :)

Thanks in advance

Derek
From: tim m on 27 May 2010 18:01
This should work though I'm not sure if it is the most efficient way.

Create a new column in the column enter this formula

=A1&" "&B2&" "&C3&" "&D4

(Put the proper number of blanks spaces inbetween the " ")

That should give you the result you seek, then copy the formula down.

NOTE: this will only work if the rest of your data follows your first
example, as in the next data is A2,B3,C4,D5 etc.

"Derek M" wrote:

> Hi all
>
> I would like to do this in excel and wonder if its possible
>
> In cell a1 i have 1234 in b2 i have an address in c3 i have 600.00 and in d4
> i have 1
>
> I have to have them concatenated but also i need 5 spaces after the first
> cell, 4 spaces after the second and 6 spaces after the third when
> concatenated (hope this makes sense)
>
> So it would look like this in a1 eventually:
>
> 1234 47 Beufort drive 600.00 1
>
> Hope someone might be abke to help as i have 685 lines of this to do :)
>
> Thanks in advance
>
> Derek
From: Steve Dunn on 28 May 2010 09:42
Derek,

you even know the name of the function required...

=CONCATENATE(A1," ",B2," ",C3," ",D4)



"Derek M" <DerekM(a)discussions.microsoft.com> wrote in message
news:B43F7AF7-8D85-4F87-84B4-575FBDA5D170(a)microsoft.com...
> Hi all
>
> I would like to do this in excel and wonder if its possible
>
> In cell a1 i have 1234 in b2 i have an address in c3 i have 600.00 and in
> d4
> i have 1
>
> I have to have them concatenated but also i need 5 spaces after the first
> cell, 4 spaces after the second and 6 spaces after the third when
> concatenated (hope this makes sense)
>
> So it would look like this in a1 eventually:
>
> 1234 47 Beufort drive 600.00 1
>
> Hope someone might be abke to help as i have 685 lines of this to do :)
>
> Thanks in advance
>
> Derek

From: Steve Dunn on 28 May 2010 09:44
Another way, making it easier to see how many spaces you are using:

=CONCATENATE(A1,REPT(" ",5),B2,REPT(" ",4),C3,REPT(" ",6),D4)


"Derek M" <DerekM(a)discussions.microsoft.com> wrote in message
news:B43F7AF7-8D85-4F87-84B4-575FBDA5D170(a)microsoft.com...
> Hi all
>
> I would like to do this in excel and wonder if its possible
>
> In cell a1 i have 1234 in b2 i have an address in c3 i have 600.00 and in
> d4
> i have 1
>
> I have to have them concatenated but also i need 5 spaces after the first
> cell, 4 spaces after the second and 6 spaces after the third when
> concatenated (hope this makes sense)
>
> So it would look like this in a1 eventually:
>
> 1234 47 Beufort drive 600.00 1
>
> Hope someone might be abke to help as i have 685 lines of this to do :)
>
> Thanks in advance
>
> Derek

 | 
Pages: 1
Prev: Linking text cells between 2 worksheets
Next: Create new records from existing ones based on 'rule'?