Hi all!!!
I've gone through a lot of the posts here to find a macro that will work for me, and I'm just not finding one. So, if anyone can help, I would really appreciate it.
(I have included a picture for reference)
I have a long string of words/phrases and numbers in each row that I need to separate into columns. Some of them will end up with more than one word in a column, some will have only 1. I know I can't do a macro for this part, because it's different each time, so I use text to columns, to separate each word/number into a separate column, and then I have another macro (Join) where I can select certain words or phrases, run the macro, and it will join them together (with a space in between each word) into the left most cell/column of the selected area. I can do this with multiple rows at a time. It doesn't join any rows, just the columns (steps 1-3b in example).
All this is fine, but, then I need to delete all of the empty cells that are now in between words (or numbers) that didn't go together. The result being, some cells have multiple words (a phrase), some have one word, some have numbers, but there are no empty cells in between. (steps 4-5)
My issue is this:
The cells I'm using can be anywhere in the spreadsheet. Most of the macros I found had a preset area (starting in B1, or columns A and B, etc). I want to be able to highlight a group of cells (multiple rows and/or columns) and have it look for the empty cells there and delete them and shift ALL other cells in those rows to the left. Even if those cells aren't in the highlighted area, they still need to shift left if they are in that row, just like standard delete cell, shift left would do. I only put four rows in the example, but usually it is a ton more, with blank cells scattered throughout the columns. I will have to do this multiple times per tab, per worksheet, and I have over 150 worksheets to organize, so, it's going to take forever...
For some reason, the macro I use to join the cells together makes it so that excel doesn't see the now "Empty/Blank" cells as truly blank. If I highlight the cells, do Ctr+G, "Special...", and then choose "Blanks", it tells me that there are no blank cells. However, if I do Ctr+F, put nothing in the "Find What:" section, check "Match entire cell contents", Click "find all", use Ctr+A to select all, then right click on one and choose delete, then shift left, it will work. It's just a bit of a hassle, since I will have to do this a LOT. Turning this process into a macro would be a huge time saver. But for some reason, whenever I try to record a macro for this, it doesn't work when I try to run it.
I have never created a macro myself, so I don't know what the issue is.
So, I need a macro for step 4.... unless there is a macro that would do steps 3 and 4 at once (join, then delete empty cells). If I could have both, that would be amazing!. If I could highlight the cells to be joined and it would automatically delete the empty ones, that would be fantastic! But it would have to work for multiple rows at a time, too (see step 3a, bottom two rows). Ideally, I would love both macros, a Join/Delete macro, and a Delete Empty Cells macro (since there will be times in the future where I know I'd use it).
Note: The example I posted has all the steps on one sheet, just so I didn't have to post multiple pictures (the steps are all labeled). Also, the borders & highlights are just for the example, there will be none in the work I am doing.
Let me know if you need any clarifications, and *Thank you* so much!!!
-Breanna
I've gone through a lot of the posts here to find a macro that will work for me, and I'm just not finding one. So, if anyone can help, I would really appreciate it.
(I have included a picture for reference)
I have a long string of words/phrases and numbers in each row that I need to separate into columns. Some of them will end up with more than one word in a column, some will have only 1. I know I can't do a macro for this part, because it's different each time, so I use text to columns, to separate each word/number into a separate column, and then I have another macro (Join) where I can select certain words or phrases, run the macro, and it will join them together (with a space in between each word) into the left most cell/column of the selected area. I can do this with multiple rows at a time. It doesn't join any rows, just the columns (steps 1-3b in example).
All this is fine, but, then I need to delete all of the empty cells that are now in between words (or numbers) that didn't go together. The result being, some cells have multiple words (a phrase), some have one word, some have numbers, but there are no empty cells in between. (steps 4-5)
My issue is this:
The cells I'm using can be anywhere in the spreadsheet. Most of the macros I found had a preset area (starting in B1, or columns A and B, etc). I want to be able to highlight a group of cells (multiple rows and/or columns) and have it look for the empty cells there and delete them and shift ALL other cells in those rows to the left. Even if those cells aren't in the highlighted area, they still need to shift left if they are in that row, just like standard delete cell, shift left would do. I only put four rows in the example, but usually it is a ton more, with blank cells scattered throughout the columns. I will have to do this multiple times per tab, per worksheet, and I have over 150 worksheets to organize, so, it's going to take forever...
For some reason, the macro I use to join the cells together makes it so that excel doesn't see the now "Empty/Blank" cells as truly blank. If I highlight the cells, do Ctr+G, "Special...", and then choose "Blanks", it tells me that there are no blank cells. However, if I do Ctr+F, put nothing in the "Find What:" section, check "Match entire cell contents", Click "find all", use Ctr+A to select all, then right click on one and choose delete, then shift left, it will work. It's just a bit of a hassle, since I will have to do this a LOT. Turning this process into a macro would be a huge time saver. But for some reason, whenever I try to record a macro for this, it doesn't work when I try to run it.
I have never created a macro myself, so I don't know what the issue is.
So, I need a macro for step 4.... unless there is a macro that would do steps 3 and 4 at once (join, then delete empty cells). If I could have both, that would be amazing!. If I could highlight the cells to be joined and it would automatically delete the empty ones, that would be fantastic! But it would have to work for multiple rows at a time, too (see step 3a, bottom two rows). Ideally, I would love both macros, a Join/Delete macro, and a Delete Empty Cells macro (since there will be times in the future where I know I'd use it).
Note: The example I posted has all the steps on one sheet, just so I didn't have to post multiple pictures (the steps are all labeled). Also, the borders & highlights are just for the example, there will be none in the work I am doing.
Let me know if you need any clarifications, and *Thank you* so much!!!
-Breanna
