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: 05:24 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: 05:24 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: 04:24 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: 05:24 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: 05:24 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 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 » |
| 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 » |
|
| | | | X Sign in to your ProZ.com account... | | | | | |