If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Concatenate with a specific number of spaces
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 |
#2
|
|||
|
|||
Concatenate with a specific number of spaces
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 |
#3
|
|||
|
|||
Concatenate with a specific number of spaces
Derek,
you even know the name of the function required... =CONCATENATE(A1," ",B2," ",C3," ",D4) "Derek M" wrote in message ... 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 |
#4
|
|||
|
|||
Concatenate with a specific number of spaces
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" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|