Split not merged cells as in Word
Dmitriy Kopnichev
Jan Karel Pieterse
>How to split not merged cells as in Word?
That is not possible. You can spread the text in one cell
over more cells by using Data, text-to-columns though.
>Please, reply to my e-mail too.
I'll trade you for it: I take the trouble to answer this
question, you take the trouble to check in regularly to
see who has responded. That way everyone can learn from
your question and the replies you get.
Regards,
Jan Karel Pieterse
Excel TA/MVP
>
Dmitriy Kopnichev
"Jan Karel Pieterse" <pieter...@compuserve.com> wrote in message news:7cff01c20635$5fa3d140$37ef2ecf@TKMSFTNGXA13...
Jan Karel Pieterse
Select the cell, choose data, text to columns, choose
fixed width, Next, drag the vertical line to where you
want the deviation. Drag any vertical lines you don't want
off the screen. Add lines by double clicking where you
want them. Then Finish.
Regards,
Jan Karel Pieterse
Excel TA/MVP
David McRitchie
Please post to a single newsgroup rather than spamming.
The single Excel newsgroup if you not know which one to
post to would be microsoft.public.excel.misc
several of the newsgroup you posted to are inappropriate.
If you knew you needed/wanted a macro you would post to
microsoft.public.excel.programming
Please read Hints for New Posters
http://www.cpearson.com/excel/newposte.htm
Not sure what you want to split off
- split after first word
- split before the last word
- split after the 3rd character
I have macro for the first to SepTerm and SepLastTerm
you would use the MID worksheet function for the last one, or
use Data, Text To Columns
http://www.mvps.org/dmcritchie/excel/join.htm
HTH, [DNS is 209.68.48.119 should the URLs below fail]
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Dmitriy Kopnichev" <ko...@hotbox.ru> wrote in message news:u0rONBjBCHA.1880@tkmsftngp04...
Jon Peltier
In addition to David's suggestions about posting etiquette, could you
also remember to post in plain text please? I opened your message,
and hunted for over a minute before I found you question buried among
the html tags. three more seconds and I was going to skip it.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______
In article <uwidyxpBCHA.2420@tkmsftngp02>, dmcri...@msn.com says...
Steve McBride
your need?
Steve
"Dmitriy Kopnichev" <ko...@hotbox.ru> wrote in message
news:u0rONBjBCHA.1880@tkmsftngp04...
Dmitriy Kopnichev
spread all cells except one to that column.
"Steve McBride" <stev...@bellsouth.net> wrote in message
news:Oic3I0zBCHA.1828@tkmsftngp05...
David McRitchie
You can merge cell borders and you can unmerge the
merged borders, but you cannot split an actual single cell container.
So along these lines you would have to merge the other cells, t
urn off gridlines, and use cell borders.
If you mean to split the contents of a cell then my previous
answer, I think, applies, and if it doesn't you will have to
be a lot more specific about what you want. This involves
using a macro and the macro you might use would depend
on what you actually want to do.
> Not sure what you want to split off
> - split after first word
> - split before the last word
> - split after the 3rd character
>
> I have macro for the first to SepTerm and SepLastTerm
> you would use the MID worksheet function for the last one, or
> use Data, Text To Columns
> http://www.mvps.org/dmcritchie/excel/join.htm
>
HTH, [DNS is 209.68.48.119 should the URLs below fail]
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Dmitriy Kopnichev" <ko...@hotbox.ru> wrote in message news:OAVkSC8BCHA.2004@tkmsftngp02...
Dmitriy Kopnichev
to the right column in a table except one. But mearging all cells is time
consuming, and it changes the formating of all that cells. In Word, it is
posible to split a cell without changing formating of other cells in a
table. I woild like to do the same in Excel.
--
Mr. Dmitriy Kopnichev
e-mail: ko...@hotbox.ru
MSN messenger: kopn...@hotmail.com
ICQ#: 135557327
Home Tel#: +7(095)1920129
Work Tel#: +7(095)7475924
Fax#: +7(095)7475924
"David McRitchie" <dmcri...@msn.com> wrote in message
news:#bxtRg9BCHA.1108@tkmsftngp04...
Jerry W. Lewis
will merge or unmerge cells without changing formatting
If you need to do this several times, consider automating the process
with a macro.
Jerry
David McRitchie
You cannot split a single cell in Excel. Excel is not Word and
Word is not Excel.
You could write a macros to help you merge columns
row by row, and to unmerge selected cells.
You will have problems with merged cells if you want to do a
sort. If you are just designing a form then there should not be
a problem.
Your changing the subject title to a lot of characters as you
redefine the problem is not likely to work well with the newsgroup archives
it will be something to watch to see what actually happens.
Macros to Merge or Split cells will be added to my web pages later
as merge.htm two such macros are shown below. Instructions to install a macro in
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Option Explicit
'David McRitchie, http://www.mvps.org/dmcritchie/code/merge.txt 2002-05-31
Sub MergeRowByRow()
'-- Merge each cell in the activecell column with the cell to the right
' limited to the usedrange (Ctrl+End)
Dim cell As Range
For Each cell In Intersect(ActiveCell.EntireColumn, _
ActiveSheet.UsedRange)
Application.DisplayAlerts = False
Range(cell, cell.Offset(0, 1)).MergeCells = True
Application.DisplayAlerts = True
Next cell
End Sub
Sub UnMergeSelected()
'-- same as format, cells, alignment (tab), (turn off) Merge Cells
Selection.MergeCells = False
End Sub
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Dmitriy Kopnichev" <ko...@hotbox.ru> wrote ..
David McRitchie
I've changed the macros, their names and how they work to simplify
usage see my website for a better explanation, but you should be able
to accomplish your task with the new macros.
http://www.mvps.org/dmcritchie/excel/merge.htm
Sub MergeRxR()
'-- Merge cells in multiple selected areas Row by Row ---
' limited to the usedrange (Ctrl+End)
Dim rng As Range
Dim rw As Range, ix As Long
Set rng = Intersect(Selection, ActiveSheet.UsedRange)
If rng Is Nothing Then
MsgBox "nothing in usedrange to be merged"
GoTo done
End If
Dim i As Long, j As Long
For i = 1 To Selection.Areas.Count
For j = 1 To Selection.Areas(i).Rows.Count
Application.DisplayAlerts = False
Selection.Areas(i).Rows(j).MergeCells = True
Application.DisplayAlerts = True
Next
Next
done:
End Sub
HTH, Let me know if this helps you or not.
Dmitriy Kopnichev
Why does your macro writes "nothing in usedrange to be merged" if I select a
range other than in upper left corner?
--
Please, click Message menu, then 'Reply to all' in OE or reply to my e-mail.
--
Mr. Dmitriy Kopnichev
e-mail: ko...@hotbox.ru
MSN messenger: kopn...@hotmail.com
ICQ#: 135557327
Home Tel#: +7(095)1920129
Work Tel#: +7(095)7475924
Fax#: +7(095)7475924
"David McRitchie" <dmcri...@msn.com> wrote in message
David McRitchie
The used range is cell A1 through what you get for Ctrl+End
If you start with an empty sheet there is nothing in the used range.
This macro checks that the active cell is within the Used Range.
Forget the macro for a moment. You merge cells vertically the same
as merging horizontally. You can do both at the same time.
In Excel 2000 at least, if you select multiple areas with the help
of Ctrl each selection in the multiple selections will be processed
separately when you merge cells
Format, cells, Alignment (tab), [x] Merge Cells
If merge cells is greyed out, it means there is a mixture of
merged and unmerged cells, just hit it twice.
If merge D2:F40 you will get one big block of merged cells.
But if you wanted D2:F2, D3:F3, D4:F4, etc. to be merged
independently you could select those ranges independently
with help of Ctrl and then do your merge with the menus.
The macro MergeRxR (Merge Row by Row) is designed to
process merge cells in each selection per row, which is
what most forms would need.
Does this answer your question, and does the macro address
your original question.
The macro and description is shown on
Merge and Unmerge Cells
http://www.mvps.org/dmcritchie/excel/merge.htm
--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Dmitriy Kopnichev" <ko...@hotbox.ru> wrote in message news:ObLYpmsCCHA.1964@tkmsftngp02...
Dmitriy Kopnichev
Thanks for explanations.
I want D2:F2, D3:F3, D4:F4 to be merged independently but all at once by
selecting range d2:f4. The same as in your macro but for columns, not for
rows.
--
Please, click Message menu, then 'Reply to all' in OE or reply to my e-mail.
"David McRitchie" <dmcri...@msn.com> wrote in message
news:urriimwCCHA.1660@tkmsftngp02...
David McRitchie
Your description for columns is exactly the same as mine for
rows. Anyway to do the opposite column by column instead
of row by row change "Rows" to "Columns" and the name
of the subroutine.
Sub MergeCxC()
'-- Merge cells in multiple selected areas Column by Column ---
' limited to the usedrange (Ctrl+End)
Dim rng As Range
Set rng = Intersect(Selection, ActiveSheet.UsedRange)
If rng Is Nothing Then
MsgBox "nothing in usedrange to be merged"
GoTo done
End If
Dim i As Long, j As Long
For i = 1 To Selection.Areas.Count
For j = 1 To Selection.Areas(i).Columns.Count
Application.DisplayAlerts = False
Selection.Areas(i).Columns(j).MergeCells = True
Application.DisplayAlerts = True
Next
Next
done:
End Sub
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Macros: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Dmitriy Kopnichev" <ko...@hotbox.ru> wrote in message news:#9DJIm4CCHA.492@tkmsftngp02...