Using a "Period" as a separator for a checklist item

[rubicon789]

All,

I've search long and hard and just have not bee able to find a solution to solve my issue. I am using Excel to create a checklist within a multi column table. I want the checklist to look uniform and i'm having trouble doing this systemically. Here is an Example:

Item #1.........Check
Item #2 which is longer name......Check
Item #3 shorter name......Check

What i want it to look like the below. Where each column is equal in width and each item is on one row with the correct number of "." characters to make it look uniform. Right now I am in the DRAFT phase, so every time i make changes to the checklist items, i need to update all the "." characters because it throws off the look.

Item #1....................................Check
Item #2 which is longer name......Check
Item #3 shorter name.................Check

Thanks in advance.

 

[jproffer]

This is......well, I don't know if it's "close enough" for you . You may have to play with the multiplier (1.3 in this case) but it's closer than it was without the multiplier, so maybe you can tweek it where you like it.

Book1
	A	B
1	short	short..............................................Check
2	longlonglonglong	longlonglonglong...............................Check
3	short	short..............................................Check
4	longlonglonglong	longlonglonglong...............................Check
5	short	short..............................................Check
6	longlong	longlong..........................................Check
7	longlonglong	longlonglong....................................Check
Sheet1

Cell Formulas
Range	Formula
B1	=A1&REPT(".",ROUND((40-LEN(A1))*1.3,0))&"Check"

 

[rubicon789]

Thanks jproffer, but, in my string i have words other than check, such as verify, On, Off etc... so check isn't a constant. Right now i just have all the items listed. Such as

Item#1..... Check
Item#2 ..... ON
Item #3 ... Verify

I just want them all to be uniform. Thanks for the help so far.

 

[Rick Rothstein]

Thanks jproffer, but, in my string i have words other than check, such as verify, On, Off etc... so check isn't a constant.

How how did you think we would have known that given what you posted in Message #1 ?

Where are those words located at with relation to the cell containing the text being checked?

 

[jtakw]

Hi,

First of all, you need to look into using Monospaced Fonts (aka Fixed-Pitch, Fixed-Width, Non-Proportional Fonts).
And I highly recommend not using the Period ( . ) as the fill.

I don't know where your data is coming from or if you're asking for a Macro to replace existing data.
If your data is manually entered, and you just want a formula to "line up" the data in a new Column, play around with my sample below:

Book1
	A	B
1	Item [EMAIL="#1@Check		Item #1 Check
2	Item #2 which is longer [EMAIL="name@On		Item #2 which is longer name On
3	Item #3 shorter [EMAIL="name@Verify		Item #3 shorter name Verify
4
5	Item [EMAIL="#1@Check		Item #1 Check
6	Item #2 which is longer [EMAIL="name@On		Item #2 which is longer name On
7	Item #3 shorter [EMAIL="name@Verify		Item #3 shorter name Verify
8
9	Non-monospaced Font	Monospaced Font (Sample using Courier New)
Sheet99

Cell Formulas
Range	Formula
B1	=LEFT(A1,FIND("@",A1)-1)&REPT(" ",50-LEN(LEFT(A1,FIND("@",A1)-1)))&TRIM(RIGHT(SUBSTITUTE(A1,"@",REPT(" ",100)),100))
B5	=LEFT(A5,FIND("@",A5)-1)&REPT(" ",50-LEN(A5))&TRIM(RIGHT(SUBSTITUTE(A5,"@",REPT(" ",100)),100))

B1 formula aligns right.
B5 formula aligns left of your last word.

EDIT: apparently HTML maker can't post the same Font I'm using, so what you see here isn't lined up, but copy and paste in your worksheet and test it out.

 

[Rick Rothstein]

	A	B
Item #1@Check
Item #2 which is longer name@On
Item #3 shorter name@Verify
Item #1@Check
Item #2 which is longer name@On
Item #3 shorter name@Verify

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]

</tbody>

Assuming the data being laid out as shown above in Column A, put this formula in cell B1...

=SUBSTITUTE(SUBSTITUTE(A1," ",CHAR(160)),"@"," ")

then bring up the Cell Format dialog box, select the Alignment tab, choose "Distributed" in the Horizontal dropdown, put a check mark in the "Justify distributed" checkbox and click the OK button... finish off by copying cell B1 down to the end of your data. You can now set the width of Column B to show a sufficient space between the longest text to the left of the @ sign and the text to the right of it.

 

[rubicon789]

Thanks for the help guy's. I still can't figure out how to accomplish what i'm trying to do.

Below is an example of some data. My list is very long, so doing a character count on each cell is to taxing. And if there are any addition or deletions, I'd need to update the formating each time.

As you can see the lengths are different, but I'm trying to set them all so they are standard. But with a formula, not one by one.
WEATHER = LEN 35
WEIGHT AND BALANCE = LEN 51

CURRENT DATA
[TABLE="width: 432"]
<tbody>[TR]
[TD]WEATHER………………………………...……....CHECKED[/TD]
[/TR]
[TR]
[TD]WEIGHT & BALANCE…………........................CHECKED[/TD]
[/TR]
[TR]
[TD]TAKEOFF/LANDING DISTANCE……...….….CALCULATE[/TD]
[/TR]
[TR]
[TD]SINGLE ENGINE RATE OF CLIMB……….....CALCULATE[/TD]
[/TR]
[TR]
[TD]SINGLE ENGINE SERVICE CEILING…...…...CALCULATE[/TD]
[/TR]
</tbody>[/TABLE]


WHAT I'M TRYING TO DO:
[TABLE="width: 659"]
<tbody>[TR]
[TD]WEATHER……………………………………...…..............CHECKED[/TD]
[/TR]
[TR]
[TD]WEIGHT & BALANCE…………...........................CHECKED[/TD]
[/TR]
[TR]
[TD]TAKEOFF/LANDING DISTANCE……...….…….....CALCULATE[/TD]
[/TR]
[TR]
[TD]SINGLE ENGINE RATE OF CLIMB………..........CALCULATE[/TD]
[/TR]
[TR]
[TD]SINGLE ENGINE SERVICE CEILING…...……....CALCULATE[/TD]
[/TR]
</tbody>[/TABLE]


I want everything to be justified and organized and having trouble doing so in a manageable way other than going line by line. The above is visually what i am going for. (Left Text) (Period Separator) (Right Text) all in a justified and organized way.