Task: You have data for labels as follows:
A B C D E FG H 1 Lastname 1stname StreetNr ZIP City FG Label 2 Meier Thomas Weg 34 23456 Bonn 3 Schulz Willi Am See 1 15623 Berlin 4 Schmidt Bernd Platz 2 28903 Calw
Solution:
Prepare column H for labels. Therefore
- H:H: Format Cells Alignment [X] Wrap text/ Vertical: Top and
- H:H: format the necessary column width for the labels.
Depending on the used printed labels (f.e. 30.4 cm paper with 8 labels on it) and the used printer, format another column with Arial, font size 89, which might have to be adjusted upon other parameters.
Fill down now:
H2: =B2&" "&A2&CHAR(10)&C2&CHAR(10)&D2&" "&E2
which could be easily changed if the application needs it.
A B C D E FG H 1 Lastname 1stname StreetNr ZIP City FG Label 2 Meier Thomas Weg 34 23456 Bonn Thomas Meier Weg 4 23456 Bonn 3 Schulz Willi Am See 1 15623 Berlin Willi Schulz Am See 1 15623 Berlin 4 Schmidt Bernd Platz 2 28903 Calw Bernd Schmidt Platz 2 28903 CalwYou see, that column H's font size can be increased as long as it fits on the label's space.
Auto or Special Filter the desired records: A great handling's advantage is the label in the same row as the underlying data!
The usage of INDEX(), ROW() and COLUMN() might be better, if different cell formats are needed. The solution above remains more simple and flexible.