Splitting a row into two when a certain substring is found

[lifeseeker1019]

Hello,

I have a huge spreadsheet that I want to clean but the idea is simple.

I want to split a row into two at the location of a substring.

An example would be:

A1: Psychiatric ward building map. Notes: this item needs attention.

I want the final result to be:

A1: Psychiatric ward building map.
A2: Notes: this item needs attention.


Sometimes the punctuation before "Notes:" is something other than a period or comma. So I think something like a search function that locates the substring "Notes:" can be used as the splitter?

Any thoughts? I'm thinking using VBA because there is a total of 10k records, but I'm new and i'm looking for some guidance

Thanks very much

[JBeaucaire]

For an accurate macro solution to be suggested the first time, you should provide an accurate sample workbook, not something dumbed down too far. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

Make sure the sample workbook fully demonstrates the needs, and BEFORE/AFTER results make it easiest.

[stanleydgromjr]

lifeseeker1019,

I assume that there would be only one Notes: in a cell in column A.


If you just have data in column A, try the following macro:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Please Login or Register  to view this content.

Then run the SplitOnNotes macro.





If there are more columns, and you want to duplicate the ROW that contains Notes: in column A, try the following macro:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Please Login or Register  to view this content.

Then run the SplitOnNotesV2 macro.

[Leith Ross]

Hello lifeseeker1019,

Here is another method. This will copy the split rows and single rows to a different worksheet leaving the original data intact. However, it is design to operate only a single column. You can easily change the starting rows of the both the destination and source worksheet and the worksheet used for each. Both are set to start with cell "A2". This assumes there are headers in row 1.

Please Login or Register  to view this content.