Excel: Merge cells while keeping the content Thread poster: Hans Lenting
|
|---|
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
| | | | Stepan Konev 
Russian Federation
Local time: 20:00
English to Russian
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
Netherlands
Member (2006)
German to Dutch
TOPIC STARTER
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:
| | | | Stepan Konev
Russian Federation
Local time: 20:00
English to Russian
| 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
Netherlands
Local time: 19:00
Member (2006)
English to 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:00
English to Russian
+ ...
| 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
Netherlands
Member (2006)
German to Dutch
TOPIC STARTER
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
Netherlands
Member (2006)
German to Dutch
TOPIC STARTER | 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:00
English to Russian
+ ...
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
Netherlands
Member (2006)
German to Dutch
TOPIC STARTER
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 | TM-Town | Manage your TMs and Terms ... and boost your translation business
Are you ready for something fresh in the industry? TM-Town is a unique new site for you -- the freelance translator -- to store, manage and share translation memories (TMs) and glossaries...and potentially meet new clients on the basis of your prior work.
More info » |
| | 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! » |
|
| | | | X Sign in to your ProZ.com account... | | | | | |
Login to reply/comment 
