A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Fixed length string of text



 
 
Thread Tools Display Modes
  #1  
Old April 16th, 2010, 08:45 PM posted to microsoft.public.excel.misc
MWysack
external usenet poster
 
Posts: 1
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  
Old April 16th, 2010, 09:00 PM posted to microsoft.public.excel.misc
Per Jessen[_2_]
external usenet poster
 
Posts: 189
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  
Old April 16th, 2010, 09:31 PM posted to microsoft.public.excel.misc
Gary Brown[_6_]
external usenet poster
 
Posts: 61
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  
Old April 16th, 2010, 10:51 PM posted to microsoft.public.excel.misc
Ron@Buy
external usenet poster
 
Posts: 348
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  
Old April 16th, 2010, 11:12 PM posted to microsoft.public.excel.misc
Ron@Buy
external usenet poster
 
Posts: 348
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  
Old April 16th, 2010, 11:14 PM posted to microsoft.public.excel.misc
T. Valko
external usenet poster
 
Posts: 15,759
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  
Old April 17th, 2010, 01:02 AM posted to microsoft.public.excel.misc
FSt1
external usenet poster
 
Posts: 2,788
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


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


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