Good Day Everyone
Can someone please help me tweak or completely rewrite the following formula to get the results I'm after. In the attached sample I'm attempting to get the info in the H column to populate the remaining columns. As you can see it works fine for the first two and last rows but once there is missing info in the H column things get messed up. Clearly the problem arises when there is no data after the colon in the source cell of each row.
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"-"&CHAR(10),""),CHAR(10),REPT(" ",LEN($A2)),COLUMNS($B:B)),": ",REPT(" ",LEN($A2)),COLUMNS($B:B)),LEN($A2)+1,LEN($A2)))
Can someone please help me tweak or completely rewrite the following formula to get the results I'm after. In the attached sample I'm attempting to get the info in the H column to populate the remaining columns. As you can see it works fine for the first two and last rows but once there is missing info in the H column things get messed up. Clearly the problem arises when there is no data after the colon in the source cell of each row.
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2,"-"&CHAR(10),""),CHAR(10),REPT(" ",LEN($A2)),COLUMNS($B:B)),": ",REPT(" ",LEN($A2)),COLUMNS($B:B)),LEN($A2)+1,LEN($A2)))
Attachments
Last edited:
