Excel: Merge cells while keeping the content Initiator des Themas: CafeTran Trainer
|
|---|
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 
Russische Föderation
Local time: 07:48
Englisch > Russisch
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]
| | | |
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
Russische Föderation
Local time: 07:48
Englisch > Russisch
| 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
Niederlande
Local time: 06:48
Mitglied (2006)
Englisch > 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: 07:48
Englisch > Russisch
+ ...
| 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.
| | | |
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?
| | | | | 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: 07:48
Englisch > Russisch
+ ...
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
| | | |
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 | Trados Business Manager Lite | Create customer quotes and invoices from within Trados Studio
Trados Business Manager Lite helps to simplify and speed up some of the daily tasks, such as invoicing and reporting, associated with running your freelance translation business.
More info » |
| | Anycount & Translation Office 3000 | Translation Office 3000
Translation Office 3000 is an advanced accounting tool for freelance translators and small agencies. TO3000 easily and seamlessly integrates with the business life of professional freelance translators.
More info » |
|
| | | | X Sign in to your ProZ.com account... | | | | | |
Login to reply/comment 
