VBA - text string to multiple cells

[omnivl]

Have searched this for the most part of today and have not found this specific question....may be a first

I have a text string in VBA that i insert in a cell on a worksheet, now this string can vary in length and may go across two pages. What i would like to do is
split this string into two cells if it goes over one page. There are pictures in the skip this row area so i cannot simply merge all rows....

For example:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Start of text string in this cell
[/TD]
[/TR]
[TR]
[TD]Skip this row
[/TD]
[/TR]
[TR]
[TD]Skip this row
[/TD]
[/TR]
[TR]
[TD]Skip this row
[/TD]
[/TR]
[TR]
[TD]Continue text string in this cell
[/TD]
[/TR]
</tbody>[/TABLE]

 

[rpaulson]

try this on a copy of your file,

adjust the ranges A1 and A5 as needed

Sub Split_me()

x = Len([A1])
y = WorksheetFunction.Find(" ", [A1], x / 2)

[A5] = Mid([A1], y + 1, x)
[A1] = Mid([A1], 1, y - 1)

End Sub

hth,
Ross

 

[omnivl]

Thanks rpaulson

This is close to what im after, the problem with this if there is only two words they will be split across two pages when they could have fitted in the first merged area
i have started a new thread to better explain