VBA to combine texts from cells to a single based on another cell value

Hi,
I need VBA to search the rows to find the text in column A if it has more than 4 chracters and another has a value of "Yes". If yes, then it will combine text from each cell to one string. If text does not have more than 4 chracters, then it will the text in that cell. There is problem for one full string which will be cross cells which have formulas and pasted vlaue(empty blanks).

Can someone help me to solve this problem.

Thanks in advance,

Hi Leith Ross,
If I unstand I need to manually to delete the fromulas in b20, C20 and D20; B22,C22 and D22?

I must have formulas in all 4 cells. I am end up with problem that I use VBA to copy and paste special (value) to those so the text is across cells.
Currently I have 70 rows and data, three rows have long texts more than 4 chracters that I need to manual delete the blank (formula) in column
B, C and D.

Can VBA clear blank cell contents? When I used VBA to copy and pasted value to the cells. I click cell B20, I see a blank but no formula; why B20 blocking
the text in A20? When I hit a delete key, then the text will cross that cell.

Thanks

Hi,
Sorry, I do have but company confidential I could not share. That why I create the representive sample. As I commented before, if I can use VBA to search the text length,
> find the row number > clear contents for cells in Col B,C,D. If I can not get it working, my backup will join all text from 4 cells, but not using clear content. The reason
I do that is I think text will look better and even space between words, not affecting by row width.

I am very appreciate your help.

Hi,
Hope if there is solution to this using VBA. Thanks