Hi Johan,

Am Wed, 20 Mar 2019 13:12:01 -0700 (PDT) schrieb :

CEll A1= AA12883;BB435;AA3621
CELL A2= AA465
CEll A3= AA62003;BB835

ps. The length could be several characters.
The challange is that all rows should have only one value in it. Then it results in

CEll A1= AA12883
CEll A2= BB435
CEll A3= AA3621

CELL A4= AA465

CEll A5= AA62003
CEll A6= BB835

if you have a new version of Office you could use TEXTJOIN to join all
cells to 1 string.
Try:

Sub Test()
Dim varData As Variant
Dim LRow As Long
Dim strTmp As String

'Modify sheet name here
With Sheets("Sheet1")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
strTmp = Application.TextJoin(";", 1, .Range("A1:A" & LRow))
varData = Split(strTmp, ";")
.Range("A1").Resize(UBound(varData) + 1) =
Application.Transpose(varData)
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016