Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 18
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 430
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 193
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 193
Default 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


  #5   Report Post  
Member
 
Posts: 47
Default Concatenate with spaces

Hello!

If you want to concatenate with spaces, you can:

=A1&" "&B1&" "C1

So in this case, you just added 5 spaces in between A1 and B1, and just 1 space between B1 and C1. same principles apply for all other situations. It just depends on how many spaces you need in between.

I learned a lot about Combining cells together he
https://www.efinancialmodels.com/kno...se-2-formulas/


Hope this helps!
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Concatenate with a specific number of spaces Derek M[_2_] Excel Worksheet Functions 0 May 27th 10 10:46 PM
format to a specific number of spaces tallguy2u Excel Discussion (Misc queries) 2 May 5th 09 08:30 PM
Concatenate Text... with spaces Leslie W. Excel Worksheet Functions 2 September 11th 08 03:21 PM
Add spaces for specific fields to paste in Corey Excel Discussion (Misc queries) 3 December 30th 05 11:22 PM
Replacing specific characters with spaces Night Owl Excel Worksheet Functions 3 May 13th 05 05:52 PM


All times are GMT +1. The time now is 01:17 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"