Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating sequential text string of fixed length | Excel Discussion (Misc queries) | |||
Text to column fixed length | Excel Discussion (Misc queries) | |||
Fixed Length, Text Formatted | Excel Discussion (Misc queries) | |||
creating numbers to a fixed length string... | Excel Worksheet Functions | |||
fixed string length,even other record is copied | Excel Discussion (Misc queries) |