This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
Hi,
Does anyone know how I could merge cells in only a limited number of rows? I have a very large excel sheet where some of the cells have been split into multiple columns and therefore shifted to the right.
I don't want to merge columns for the entire sheet, because it is only a limited number of cells/columns that are affected.
The format of rows 750-752 is correct. Notice how in rows 753 and 754, columns 2, 3, and 4 should be combined into one column (column #2), column #5 should then be shifted to column #3, column #6 should be shifted to column #4, etc. Basically, in rows 753 and 754, there are 2 extra columns at the end because of this split/shift.
So this issue is about both merging cells and shifting cells, but not across the entire spreadsheet.
Any help is appreciated. This issue is from a system-generated report that I get frequently, so I'm trying to make the solution as replicable as possible.
Solved! Go to Solution.
What consistent criteria can we use to determine if column 2 is split or not? Is it something like there's data in column X because of the shift and no data in that column for non shifted rows?
Also any chance of getting sample data
Dan
Hi @danilang,
Thanks for the question.
I know column 2 is split if the subsequent columns have leading spaces. In my example, columns 3 and 4 have leading spaces because they're supposed to be part of column 2 as one sentence. But also, to your point, another way of determining if it's split is looking to see if there is data is two columns at the end (columns M and N). If there is data present in these columns, it means something was incorrectly split, causing everything to shift to the right.
Thanks!
Is it always column 2 that gets split or can it be any column? Also, is it always spilt into 3 columns or can it be more or less?
Dan
Assuming it's always column 2 and it gets split into 3 columns, the following workflow shows the idea. You'll need to adjust it based on the number of columns in your actual report
1. Start by adding a record id.
2. Split based on leading space in cell 3, for the affected rows(top branch)
3. Merge col 2, 3, and 4 into 2.
4 Union all based on column position
5 resort
6 clean up . this is where you specify which columns are extra based on your actual data
Dan
That helps a lot. Thank you!