Removing blank cells and shifting adjoining cells left

[manc]

Dear Demigods,

Really getting frustrated with this! Every single search for the answer seems to return an answer for achieving them same but based on rows.
There doesn't seem to be one post to achieve the same with empty cells in columns.

So this is the post i am finding everywhere:

Column A
1 hi
2 there
3
4 I
5 am
6
7 trying
8 to
9 achieve
10
11 this

And the formula posts the results in column B:
1 hi
2 there
3 I
4 am
5 trying
6 to
7 achieve
8 this

However, my issue is instead of the information all in column A, rows 1-8, my information is in column A-G.
So it's the same problem, just transposed, and i'm using columns instead of rows.
For example:
A1 HI
B1 THERE
C1
D1 I'M
E1 TRYING
F1
G1TO
etc,etc.

And the solution should be:

H1 HI
I1 THERE
J1 I'M
K1 TRYING
etc, etc.

The idea is I can then apply the same formula to rows 2, 3, 4 onwards....

Please help. Been sat here for 4 hours trying to find the answer myself.

Best regards
frustrated manc

 

[footoo]

[FONT=&quot]=IFERROR(INDEX($A2:$H2,AGGREGATE(15,6,COLUMN($A:$H)/[/FONT][FONT=&quot]NOT(ISBLANK($A2:$H2))[/FONT][FONT=&quot],COLUMNS($A:A))),"")

Taken from here : [/FONT]https://www.myonlinetraininghub.com/excel-forum/excel/extract-values-from-non-blank-cells-in-a-row

 

[mikerickson]

If you leave column H blank (perhaps hidden) and put this formula in I1 and drag right, it should do what you want

=INDEX($A$1:$H$1, 1, MIN(8, COLUMN(A1)+COUNTBLANK($A1:A1)))&""

 

[manc]

Thanks guys,

By following the link that footoo provided, i was able to recreate the formula in google sheets which is where i wanted it for.

Thanks again.
Best regards
manc