Excel: Merge cells while keeping the content Iniziatore argomento: Hans Lenting
|
|---|
Hans Lenting
Paesi Bassi
Membro (2006)
Da Tedesco a Olandese
Handy to correct alignment tables, e.g. from this free service .
Sub MergeCellsKeepingContents()
Dim val As String
Dim rng As Range
Set rng = Selection
For Each Cell In rng
val = val & " " & Cell.Value
Next Cell
With rng
.Merge
.Value = Trim(val)
.WrapText = True
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
End With
End Sub
| | | |
Thank you.
Update: weirdly it adds a dozen of spaces between segments after merging them so that I have to delete them manually, which is not much different from deleting line breaks that appear after normal merging.
[Edited at 2022-04-01 16:00 GMT]
| | | | Hans Lenting
Paesi Bassi
Membro (2006)
Da Tedesco a Olandese
AVVIO ARGOMENTO
What did your table look before you ran the macro?
Anyway, here's another demo. Note that you can assign the macro to a keyboard shortcut:
| | | | | Original spreadsheet | Apr 2, 2022 |
Yes, I assigned a shortcut. Originally, the table looked like this:
There are no spaces in individual cells.
However it doesn't seem to happen with all files. Most of them merge as expected, with only one space in between. So I will definitely use this macro. Thank you.
| | |
|
|
|
Samuel Murray 
Paesi Bassi
Local time: 18:49
Membro (2006)
Da Inglese a Afrikaans
+ ...
| AutoIt solution | Apr 2, 2022 |
But if I understand correctly, the macro creates merged cells, which can sometimes be a pain to deal with when you convert to e.g. tab-delimited text.
Sorry to hijack your thread... but...
FWIW, I sometimes need to align two columns (e.g. source and target) in an Excel file, too, and for this, I've written a little AutoIt script to mimic the functions of the PlusTools aligner. [One has to test the actions on one's own computer, of course, as I have found that different... See more But if I understand correctly, the macro creates merged cells, which can sometimes be a pain to deal with when you convert to e.g. tab-delimited text.
Sorry to hijack your thread... but...
FWIW, I sometimes need to align two columns (e.g. source and target) in an Excel file, too, and for this, I've written a little AutoIt script to mimic the functions of the PlusTools aligner. [One has to test the actions on one's own computer, of course, as I have found that different versions of Excel need different instructions.] To use the script, you click a cell and then press a shortcut. Here are the existing shortcuts:
Ctrl + ? = Show shortcuts
Ctrl + Q = Exit script
Ctrl + ENTER = Edit/exit cell (same as F2 in Excel)
Ctrl + M = Merge with next cell (and move subsequent cells up)
Ctrl + Shift + M = Merge with next cell (without moving subsequent cells up)
*Ctrl + Shift + S = Split cell at cursor position (and create new cell under the current cell, and move subsequent cells down)
Ctrl + I = Insert new cell in current position (and move current cell down)
Ctrl + D = Delete current cell (and move subsequent cells up)
Ctrl + Shift + I = Insert cell below (and move subsequent cells down)
Ctrl + Shift + D = Delete previous cell (and move subsequent cells up)
http://www.leuce.com/autoit/Alignment%20fixer%20for%20Excel%20v2.zip
* In Excel 2003 I can use Ctrl + S for splitting, but in Excel 365, Ctrl +S is hardcoded to "Save file" and unhijackable. ▲ Collapse
| | | | mikhailo
Local time: 20:49
Da Inglese a Russo
+ ...
| Function join_content_of selected_cells is needed more often | Apr 2, 2022 |
Due to Excel restrictions on manipulations with merged cells function JoinContentsOfSelectedCells (to first selected cell) is needed more often.
| | | | Hans Lenting
Paesi Bassi
Membro (2006)
Da Tedesco a Olandese
AVVIO ARGOMENTO
mikhailo wrote:
Due to Excel restrictions on manipulations with merged cells function JoinContentsOfSelectedCells (to first selected cell) is needed more often.
Samual also mentioned these restrictions. Can you please elaborate?
And do you have such a Join function available?
| | | | Hans Lenting
Paesi Bassi
Membro (2006)
Da Tedesco a Olandese
AVVIO ARGOMENTO | Platform-independent | Apr 3, 2022 |
Samuel Murray wrote:
Sorry to hijack your thread... but...
Not a problem, if it serves mankind  .
I've written a little AutoIt script to mimic the functions of the PlusTools aligner.
I'll have a look at it (thanks, BTW), but I wouldn't be able to use it on my version of Excel:
VBA has the advantage of being platform-independent.
Instead of AutoIt (or AutoHotkey) I'd have to use Keyboard Maestro on my Mac.
| | |
|
|
|
mikhailo
Local time: 20:49
Da Inglese a Russo
+ ...
German-Dutch Engineering Translation wrote:
Can you please elaborate?
And do you have such a Join function available?
From https://www.cyberforum.ru/vba/thread2519488.html
Sub JoinContentsOfSelectedCells()
Dim delim As String, newdata As String
Dim rng As Range
If TypeName(Selection) "Range" Or Selection.Count
| | | | Hans Lenting
Paesi Bassi
Membro (2006)
Da Tedesco a Olandese
AVVIO ARGOMENTO
mikhailo wrote:
German-Dutch Engineering Translation wrote:
Can you please elaborate?
And do you have such a Join function available?
From https://www.cyberforum.ru/vba/thread2519488.html
Sub JoinContentsOfSelectedCells()
Dim delim As String, newdata As String
Dim rng As Range
If TypeName(Selection) <> "Range" Or Selection.Count <= 1 Then Exit Sub
delim = " "
newdata = ""
For Each rng In Selection
newdata = newdata & rng.Value & delim
Next rng
Application.DisplayAlerts = False
Selection.Merge
Selection = Left(newdata, Len(newdata) - Len(delim))
Application.DisplayAlerts = True
Selection.UnMerge
End Sub
[Edited at 2022-04-04 04:16 GMT]
| | | | To report site rules violations or get help, contact a site moderator: You can also contact site staff by submitting a support request » Excel: Merge cells while keeping the content | Wordfast Pro | Translation Memory Software for Any Platform
Exclusive discount for ProZ.com users!
Save over 13% when purchasing Wordfast Pro through ProZ.com. Wordfast is the world's #1 provider of platform-independent Translation Memory software. Consistently ranked the most user-friendly and highest value
Buy now! » |
| | Protemos translation business management system | Create your account in minutes, and start working! 3-month trial for agencies, and free for freelancers!
The system lets you keep client/vendor database, with contacts and rates, manage projects and assign jobs to vendors, issue invoices, track payments, store and manage project files, generate business reports on turnover profit per client/manager etc.
More info » |
|
| | | | X Sign in to your ProZ.com account... | | | | | |
Login to reply/comment 
