Hi All,
having done some investigations myself, I have Not found a solution for my
problem using a Formula, However I have found a way around it using a Macro
instead.
Basically Move a cell to the left, find a non-Blank cell, copy and paste
into the cell where You started.
A Most Warmhearted Thank You to Roger for trying to help!
Hilvert

The Macro Code I used is:

alphabet1 = ActiveCell.Address
numRows = Selection.Rows.Count
numColumns = Selection.Columns.Count
ActiveCell.Offset(0, -1).Select
Selection.Resize(numRows + 20, numColumns).Select
Selection.Find(What:="*", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Select
alphabet22 = ActiveCell.Address
Selection.Copy
Range(alphabet1).Select
ActiveSheet.Paste



"Hilvert Scheper" wrote:

Hi there Dear specialists,

Can I Please have some help with this;
I want a Formula that finds and returns the Text-string from the next
Non-Blank Cell in the previous column. The reason why I'm asking is that this
is part of a Pivot-Table due to which some cells are empty and the number of
empty cells is Variable.

Many Thanks for Your help, I hope this example helps:

Column A Column B
(blank) This Cell (B1) needs to find the next non-blank cell in column
A,
(blank) and return text-string "SN2 2QH" from Cell A4
(blank)
SN2 2QH

Thank You again,
Hilvert