I want to manipulate text in excel. The text is broken down to cells, each row has up to 8 cells (columns A to H), including blank cells. I am trying to find a macro that will fill the 9th cell in each row with the last cell on the same row excluding the blank cells.
starting from A1, if row 1 (each  represents a cell) =
[Brook Close][SO12 1SP] then I want in cell J1 to get the text from cell A3 .
However if row 2 =
[Rose rd][Bitterne Park][Bitterne][Soton][HANTS][SO18 1PS] then in cell J2 I want to get the text from G2.
The blanks are always at the end of each row.