Removing blank cells and shifting adjoining cells left

manc

Active Member
Joined
Feb 26, 2010
Messages
340
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
 
Last edited:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
[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
 
Upvote 0
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)))&""
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top