text columns in one cell

  • Thread starter Thread starter DzEK
  • Start date Start date
D

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
 
You have to format the formula cells to use a fixed width font. In the
example I used COURIER.
 
in format cell choose font and set courier for whole column
but still don't work!!

any idea?

many thanks for your attention!
 
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.
 
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)
 
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)
 
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!
 
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.
 
Back
Top