aligning text ... i.e. removing blank cells

I am exporting data into a spreadsheet but can't get it to consistently go into same columns - including left hand label column
If I insert a column A, at Is there a formula that then looks to the right and when it finds a cell with text moves it into column A
Thanks Neil

Thanks for your help but when I entered it I got #value
I placed it in A1 and put some text in c1