Vinhdang,

Try the macro below.

HTH,
Bernie
MS Excel MVP

Sub MacroForVinhdang()
Dim myR As Range
Dim myC As Range
Dim myX As Range

Set myR = Range("A:A")

Set myC = myR.Find(What:="@", LookAt:=xlPart)

While Not myC Is Nothing
Set myX = Range(myC, myC.End(xlUp))
myX.Copy
myC(1, 2).PasteSpecial Paste:=xlPasteValues, Transpose:=True
myX.ClearContents
Set myC = myR.FindNext
Wend

Range("B:B").SpecialCells(xlCellTypeBlanks).Entire Row.Delete
Range("A:A").Delete

End Sub


wrote in message ups.com...
I need help with the transpose function. Currently I have 2000 columns
with data of people's names. For example

Jane Doe
Partner
J&J LLC
123 Four Street, NY, NY 12345
(123)456-789


Doe, John
Lawyer
J&J LLC
123 Four Street, NY, NY 12345
(123)456-789


I wish to transpose the data from a column to row with a delimiter
using that email address (because not all of the data has 6 rows of
data)

Is this possible without VisualBasic? Such as a function that will
notice the "@" symbol and transpose the next group of data into a new
row.

The output should be like this

Row One:
Jane Doe | Partner | J&J LLC | 123 Four Street, NY, NY 12345 |
(123)456-789 |
Row Two:
Doe, John | Lawyer | | J&J LLC | 123 Four Street, NY, NY 12345 |
(123)456-789 |

and so forth.

If you have any suggestions or websites I can check out, you have my
gratitude!