text columns in one cell

[DzEK]

does anybody know how to write in ''two columns in one cell'' ?
for better understanding my problem, i'll try to describe:
need to write first and last name in one cell
when write another name in second row, i want to have
firt character of name under first character of name in first row and
first character of last name under first character of last name in first row

inserting and merging columns is not impossible

thanks

 

[T. Valko]

Hmmm...

You could do this with a helper column and set the helper column to use a
fixed width font like Courier.

See this screencap:

http://img156.imageshack.us/img156/2203/alignedzk0.jpg

In the formula, change the 7 to the length of the *longest* first name. In
the screencap William is the longest first name and is 7 characters long.

 

[DzEK]

that's what i need!!
i type exactly, but don't work !!!
:-(
works with office 2000?

 

[T. Valko]

You have to format the formula cells to use a fixed width font. In the
example I used COURIER.

 

[DzEK]

in format cell choose font and set courier for whole column
but still don't work!!

any idea?

many thanks for your attention!

 

[T. Valko]

What does "don't work" mean?

Post the *exact* formula you tried.

This will only work on 2 word names like John Smith. If you have 3 or even 4
word names I don't know how you'd do this.

 

[DzEK]

hi, valko

i wrote ''don't work'' because i got message - formula error !!

this is 'formula' (copy -paste)


=LEFT(A1,FIND(" ",A1)-1&REPT(" ",8-LEN(LEFT(A1,FIND("
",A1)-1))+1)&MID(A1,FIND(" ",A1)+1,255)

 

[T. Valko]

Ok, you're missing a ")"

Here is the formula in "chunks" so that line wrap (hopefully) won't remove
the *necessary* space characters. Also note, I've made a slight change by
removing "+1" in two places:

=LEFT(A1,FIND(" ",A1)-1)&
REPT(" ",8-LEN(LEFT(A1,FIND(" ",A1)-1)))&
MID(A1,FIND(" ",A1),255)

Also of note, we can calculate which first name is the longest and build
that into the formula *but* this will add more complexity to the formula and
will make it an array formula**:

=LEFT(A1,FIND(" ",A1)-1)&
REPT(" ",MAX(LEN(LEFT(A$1:A$3,FIND(" ",A$1:A$3)-1)))
-LEN(LEFT(A1,FIND(" ",A1)-1)))&
MID(A1,FIND(" ",A1),255)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Or, you can use a helper cell to calculate the longest first name and then
refer to that cell:

B1 = array formula** :

=MAX(LEN(LEFT(A1:A3,FIND(" ",A1:A3)-1)))

Then (normally entered):

=LEFT(A1,FIND(" ",A1)-1)&
REPT(" ",B$1-LEN(LEFT(A1,FIND(" ",A1)-1)))&
MID(A1,FIND(" ",A1),255)

 

[DzEK]

i tried your suggestion, but nothing happened...except formula error!

=LEFT(A1,FIND(" ",A1)-1&REPT(" ",8-LEN(LEFT(A1,FIND("
",A1)-1))+1)&MID(A1,FIND(" ",A1)+1,255)


A1,FIND became blackmarked!

also A3,FIND in 'your' formula

=MAX(LEN(LEFT(A1:A3,FIND(" ",A1:A3)-1)))


any new idea?

thanks in advance!

 

[David Biddulph]

If you are getting errors flagged at that stage, I wonder whether your
Windows Regional Settings are defining the list separator as semi-colon
instead of comma?
If so, you'll need to separate parameters in your formulae by semi-colons
instead of commas.

 

[T. Valko]

Here's a small sample file that demonstrates this:

align.xls 15kb

http://cjoint.com/?ccxSV3KVt3

 

[DzEK]

i made change you suggest

now have another error

#VALUE!


???

:-(

##$%&#!!

 

[DzEK]

yes !!!
works!

have no words to express my thankfulness

:-D

 

[T. Valko]

You're welcome. Thanks for the feedback!