Need to Combine Lines in Macro

[adamrx83]

Does anyone know how to combine cells containing text to the end of previously defined cells (in this case cells starting with a #) by using an Excel function or macro? I am trying to clean up med lists hundreds of drugs. Directions are scattered across multiple lines. Essentially a nice clean list with one cell per medication with instructions. Any insight on creating a macro for this would be greatly appreciated.

1) ASPIRIN TAB,CHEWABLE 81MG TAKE ONE TABLET
BY MOUTH DAILY FOR HEART
PROTECTION
2) ATORVASTATIN TAB 40MG TAKE ONE TABLET
BY MOUTH DAILY TO LOWER CHOLESTEROL
3) BUDESONIDE/FORMOTEROL INHL,ORAL INHALE TWO PUFFS
BY MOUTH TWICE DAILY. ENSURE TO RINSE
MOUTH AFTER EACH USE. FOR COPD
4) CHLORTHALIDONE TAB 12.5MG
TAKE ONE TABLET BY MOUTH DAILY FOR HYPERTENSION
5) CIPROFLOXACIN TAB 500MG TAKE ONE TABLET BY MOUTH
EVERY DAY FOR INFECTION
6) FERROUS SULFATE TAB 325MG BY MOUTH TAKE ONE
TABLET BY MOUTH
EVERY DAY ON AN EMPTY STOMACH
7) FLUNISOLIDE INHL,NASAL INSTILL 2 SPRAYS IN
EACH NOSTRIL TWICE DAILY
8) GABAPENTIN CAP,ORAL 600MG TAKE ONE CAPSULE BY MOUTH
EVERY 12 HOURS FOR NERVE PAIN
9) HALOPERIDOL INJ,SOLN 5MG/1ML INJECT 1ML
IM EVERY 8 HOURS AS NEEDED FOR AGITATION
10) HEPARIN INJ,SOLN 5000 UNIT/ML INJECT 1ML (5000 UNITS)
SQ BID (Q12H)
11) HYDRALAZINE INJ,SOLN 10MG/0.5ML IVP Q4HRS PRN
SBP>180
12) MIRTAZAPINE TAB 15MG TAKE ONE TABLET
BY MOUTH EVERY NIGHT AT BEDTIME FOR DEPRESSION
13) MORPHINE TAB,IR 15MG TAKE ONE TABLET BY MOUTH EVERY
SIX HOURS AS NEEDED FOR BREAKTHROUGH PAIN
14) MORPHINE TAB,SA 30MG TAKE ONE TABLET
BY MOUTH TWICE DAILY FOR PAIN

[6StringJazzer]

This macro reads what is in the first column and writes the merged lines to the second column. Here is it implemented in your file.

It assumes that the first line always starts a numbered item. It reads the lines in column A and outputs the merged lines in column B. It works for up to 999 numbered items.

[adamrx83]

Thank you!!

[jindon]

Same result but different method,

Please Login or Register  to view this content.

[adamrx83]

How can I change the input and output columns in this code?

Option Explicit

Please Login or Register  to view this content.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

[jindon]

Change "a" for input column.

Please Login or Register  to view this content.

change 4 for output column.

Please Login or Register  to view this content.