G

Guest

Hello everybody,

I have a single column of texts with the following pattern:
Row 1: Company Name
Row 3: Tel
Row 4: Fax
Row 5: Website

For example:

ABC Company Ltd
20/F, Abc Building, 1 Abc Street, ABC Country
1111 1111
2222 2222
www.Abc.com.
XYZ Ptd Ltd
Suite 5008 â€“ 5010, 50/F, XYZ Building, 7 XYZ Street, XYZ Country
3333 3333
4444 4444
www.xyz.com
(and so on â€¦)

The length of such column is not fixed.

So can I use â€œPaste Special -> Transposeâ€ or =TRANSPOSE(array) to transform
the data that column A contains all company names, column B contains all
addresses, column C contains all tel num. and so on?

(The â€œPaste Special -> Transposeâ€ transformed all data in a single row. But
I want to transform the record line â€“ by â€“ line without moving again â€¦)

Thank you!!

M

Max

One way to try ..

Assuming source data is in col A, A1 down,
in groups of 5 rows each as indicated
(w/o any intervening blank rows)

Put in say, B1:

=OFFSET(\$A\$1,ROWS(\$A\$1:A1)*5-5+COLUMNS(\$A\$1:A1)-1,)

Copy B1 across 5 cols to F1, fill down until zeros appear signalling
exhaustion of data from col A

The above will return the desired results in cols B to F

G

Guest

Dear Max,

Thanks a lot for your help, the formula works fine!!

Much thx!!

Florence

M

Max

You're welcome, Florence !
Glad to hear it worked for you
Thanks for the feedback