Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3
Default Fixed length string of text

Hello,
I am trying to create strings of text by combining cells using CONCATENATE.
I need to have each string of text to be the same length. Below are some
examples of the text strings:

A1= 0000001
B1=123456789
C1= Doe
D1= John

A2=0000002
B2=987654321
C2=Smith
D2=John

In need the lines to look like this:
0000001123456789Doe John
0000002987654321Smith John

This is what they look like when I use CONCATENATE:
0000001123456789DoeJohn
0000002987654321SmithJohn

Is there anyway to get these strings of text to look like this without
manually adding the spaces for each line?

Thanks,
Megan

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 703
Default Fixed length string of text

Hi Megan

Look at this:

=A1&B1&C1&LEFT(" ",30-LEN(A1&B1&C1&D1))&D1

Regards,
Per

On 16 Apr., 21:45, MWysack wrote:
Hello,
I am trying to create strings of text by combining cells using CONCATENATE.
I need to have each string of text to be the same length. Below are some
examples of the text strings:

A1= 0000001
B1=123456789
C1= Doe
D1= John

A2=0000002
B2=987654321
C2=Smith
D2=John

In need the lines to look like this:
0000001123456789Doe * * * * John
0000002987654321Smith * * *John

This is what they look like when I use CONCATENATE:
0000001123456789DoeJohn
0000002987654321SmithJohn

Is there anyway to get these strings of text to look like this without
manually adding the spaces for each line?

Thanks,
Megan


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 126
Default Fixed length string of text

Assuming you want the Last name to be exactly 9 characters...
=CONCATENATE(A1,B1,C1&IF(LEN(C1)<=9,REPT(" ",9-LEN(C1)),""),D1)
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"MWysack" wrote:

Hello,
I am trying to create strings of text by combining cells using CONCATENATE.
I need to have each string of text to be the same length. Below are some
examples of the text strings:

A1= 0000001
B1=123456789
C1= Doe
D1= John

A2=0000002
B2=987654321
C2=Smith
D2=John

In need the lines to look like this:
0000001123456789Doe John
0000002987654321Smith John

This is what they look like when I use CONCATENATE:
0000001123456789DoeJohn
0000002987654321SmithJohn

Is there anyway to get these strings of text to look like this without
manually adding the spaces for each line?

Thanks,
Megan

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 345
Default Fixed length string of text

MWysack
You have a considerable problem here to give a visual look to the line as
you show it. Consider the width of the letters 'm' & 'i' so a name with a
double 'm' will visually appear wider than a name with 'i's in it.
Anyhow try this as being the best I think you may get for variable lengths
of names and widths of letters:
=REPLACE(A1&B1&C1&D1,LEN(A1&B1&C1)+1,0,REPT(" ",30-LEN(A1&B1&C1)))


"MWysack" wrote:

Hello,
I am trying to create strings of text by combining cells using CONCATENATE.
I need to have each string of text to be the same length. Below are some
examples of the text strings:

A1= 0000001
B1=123456789
C1= Doe
D1= John

A2=0000002
B2=987654321
C2=Smith
D2=John

In need the lines to look like this:
0000001123456789Doe John
0000002987654321Smith John

This is what they look like when I use CONCATENATE:
0000001123456789DoeJohn
0000002987654321SmithJohn

Is there anyway to get these strings of text to look like this without
manually adding the spaces for each line?

Thanks,
Megan

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 345
Default Fixed length string of text

Small modification:
=REPLACE(A1&B1&C1&D1,LEN(A1&B1&C1)+1,0,REPT(" ",59-LEN(A1&B1&C1&C1)))
Might give a slightly better look

"Ron@Buy" wrote:

MWysack
You have a considerable problem here to give a visual look to the line as
you show it. Consider the width of the letters 'm' & 'i' so a name with a
double 'm' will visually appear wider than a name with 'i's in it.
Anyhow try this as being the best I think you may get for variable lengths
of names and widths of letters:
=REPLACE(A1&B1&C1&D1,LEN(A1&B1&C1)+1,0,REPT(" ",30-LEN(A1&B1&C1)))


"MWysack" wrote:

Hello,
I am trying to create strings of text by combining cells using CONCATENATE.
I need to have each string of text to be the same length. Below are some
examples of the text strings:

A1= 0000001
B1=123456789
C1= Doe
D1= John

A2=0000002
B2=987654321
C2=Smith
D2=John

In need the lines to look like this:
0000001123456789Doe John
0000002987654321Smith John

This is what they look like when I use CONCATENATE:
0000001123456789DoeJohn
0000002987654321SmithJohn

Is there anyway to get these strings of text to look like this without
manually adding the spaces for each line?

Thanks,
Megan



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default Fixed length string of text

best I think you may get for variable lengths
of names and widths of letters


You can get it aligned very nicely if you use a fixed width font like
Courier New. You'd have to define the min number of spaces you want between
the strings.

--
Biff
Microsoft Excel MVP


"Ron@Buy" wrote in message
...
MWysack
You have a considerable problem here to give a visual look to the line as
you show it. Consider the width of the letters 'm' & 'i' so a name with a
double 'm' will visually appear wider than a name with 'i's in it.
Anyhow try this as being the best I think you may get for variable lengths
of names and widths of letters:
=REPLACE(A1&B1&C1&D1,LEN(A1&B1&C1)+1,0,REPT(" ",30-LEN(A1&B1&C1)))


"MWysack" wrote:

Hello,
I am trying to create strings of text by combining cells using
CONCATENATE.
I need to have each string of text to be the same length. Below are some
examples of the text strings:

A1= 0000001
B1=123456789
C1= Doe
D1= John

A2=0000002
B2=987654321
C2=Smith
D2=John

In need the lines to look like this:
0000001123456789Doe John
0000002987654321Smith John

This is what they look like when I use CONCATENATE:
0000001123456789DoeJohn
0000002987654321SmithJohn

Is there anyway to get these strings of text to look like this without
manually adding the spaces for each line?

Thanks,
Megan



  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,942
Default Fixed length string of text

hi
i did this a tad different.
assuming data in A1 and B1 would be standart length.....
in E1, put this to establish a template.
=A1&B1&C1&" "&D1 with 6 spaces to start. change if needed.
using E1 as a template in E2, enter....
=A2&B2&C2&REPT(" ",LEN(E$1)-LEN(A2&B2&C2&D2))&D2
all cells below E1 ended up with the same number of characters. i tested 10.
and using Biff's suggestion put a nice square edge to it all.

downside. longest name must be in E1 else error will occur further down the
list.
not sure if that is a problem.

regards
FSt1

"MWysack" wrote:

Hello,
I am trying to create strings of text by combining cells using CONCATENATE.
I need to have each string of text to be the same length. Below are some
examples of the text strings:

A1= 0000001
B1=123456789
C1= Doe
D1= John

A2=0000002
B2=987654321
C2=Smith
D2=John

In need the lines to look like this:
0000001123456789Doe John
0000002987654321Smith John

This is what they look like when I use CONCATENATE:
0000001123456789DoeJohn
0000002987654321SmithJohn

Is there anyway to get these strings of text to look like this without
manually adding the spaces for each line?

Thanks,
Megan

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Fixed length string of text

On Saturday, 17 April 2010 08:31:02 UTC+12, Gary Brown wrote:
Assuming you want the Last name to be exactly 9 characters...
=CONCATENATE(A1,B1,C1&IF(LEN(C1)<=9,REPT(" ",9-LEN(C1)),""),D1)
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"MWysack" wrote:

Hello,
I am trying to create strings of text by combining cells using CONCATENATE.
I need to have each string of text to be the same length. Below are some
examples of the text strings:

A1= 0000001
B1=123456789
C1= Doe
D1= John

A2=0000002
B2=987654321
C2=Smith
D2=John

In need the lines to look like this:
0000001123456789Doe John
0000002987654321Smith John

This is what they look like when I use CONCATENATE:
0000001123456789DoeJohn
0000002987654321SmithJohn

Is there anyway to get these strings of text to look like this without
manually adding the spaces for each line?

Thanks,
Megan


This solution just worked fantastically for me - thanks
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
Creating sequential text string of fixed length dhstein Excel Discussion (Misc queries) 7 December 10th 08 03:52 AM
Text to column fixed length Luci Excel Discussion (Misc queries) 6 November 27th 08 06:27 PM
Fixed Length, Text Formatted Novice2000 Excel Discussion (Misc queries) 2 May 13th 08 06:06 PM
creating numbers to a fixed length string... ds Excel Worksheet Functions 4 July 5th 07 01:06 AM
fixed string length,even other record is copied viv Excel Discussion (Misc queries) 0 May 30th 05 08:28 PM


All times are GMT +1. The time now is 04:37 AM.

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"