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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|