Split text to rown
-1
I am using Excel 2010 and I am having the following data:
As you can see in one row there is multiple data.
My data for product one looks in the cell like that:
Product 1.600,00
Other 1.600,00
EH-Price 3.200,00
Pos.-Price 3.200,00
I would like to split the data the following way:
I tried to transpose the data and then separate it by "space" and then transpose it back, however this does not work well as there are multiple lines.
Please further find below a test file:
Testing file
I highly appreciate your replies!
excel vba excel-vba
edited Nov 28 '18 at 7:46
Pᴇʜ
23.9k63052
asked Nov 28 '18 at 7:37
Anna.KleeAnna.Klee
1,2581165135
add a comment |
-1
I am using Excel 2010 and I am having the following data:
As you can see in one row there is multiple data.
My data for product one looks in the cell like that:
Product 1.600,00
Other 1.600,00
EH-Price 3.200,00
Pos.-Price 3.200,00
I would like to split the data the following way:
I tried to transpose the data and then separate it by "space" and then transpose it back, however this does not work well as there are multiple lines.
Please further find below a test file:
Testing file
I highly appreciate your replies!
excel vba excel-vba
edited Nov 28 '18 at 7:46
Pᴇʜ
23.9k63052
asked Nov 28 '18 at 7:37
Anna.KleeAnna.Klee
1,2581165135
Have a look here How to Split a single cell into multiple rows and add another row. The technique you will need is very similar. To split by the linebreaks usevbLfas delimiter in theSplit()function.
– Pᴇʜ
Nov 28 '18 at 8:32
add a comment |
-1
-1
-1
I am using Excel 2010 and I am having the following data:
As you can see in one row there is multiple data.
My data for product one looks in the cell like that:
Product 1.600,00
Other 1.600,00
EH-Price 3.200,00
Pos.-Price 3.200,00
I would like to split the data the following way:
I tried to transpose the data and then separate it by "space" and then transpose it back, however this does not work well as there are multiple lines.
Please further find below a test file:
Testing file
I highly appreciate your replies!
excel vba excel-vba
edited Nov 28 '18 at 7:46
Pᴇʜ
23.9k63052
asked Nov 28 '18 at 7:37
Anna.KleeAnna.Klee
1,2581165135
I am using Excel 2010 and I am having the following data:
As you can see in one row there is multiple data.
My data for product one looks in the cell like that:
Product 1.600,00
Other 1.600,00
EH-Price 3.200,00
Pos.-Price 3.200,00
I would like to split the data the following way:
I tried to transpose the data and then separate it by "space" and then transpose it back, however this does not work well as there are multiple lines.
Please further find below a test file:
Testing file
I highly appreciate your replies!
excel vba excel-vba
excel vba excel-vba
edited Nov 28 '18 at 7:46
Pᴇʜ
23.9k63052
asked Nov 28 '18 at 7:37
Anna.KleeAnna.Klee
1,2581165135
edited Nov 28 '18 at 7:46
Pᴇʜ
23.9k63052
asked Nov 28 '18 at 7:37
Anna.KleeAnna.Klee
1,2581165135
edited Nov 28 '18 at 7:46
Pᴇʜ
23.9k63052
edited Nov 28 '18 at 7:46
Pᴇʜ
23.9k63052
edited Nov 28 '18 at 7:46
Pᴇʜ
23.9k63052
23.9k63052
asked Nov 28 '18 at 7:37
Anna.KleeAnna.Klee
1,2581165135
asked Nov 28 '18 at 7:37
Anna.KleeAnna.Klee
1,2581165135
asked Nov 28 '18 at 7:37
Anna.KleeAnna.Klee
1,2581165135
1,2581165135
Have a look here How to Split a single cell into multiple rows and add another row. The technique you will need is very similar. To split by the linebreaks usevbLfas delimiter in theSplit()function.
– Pᴇʜ
Nov 28 '18 at 8:32
add a comment |
Have a look here How to Split a single cell into multiple rows and add another row. The technique you will need is very similar. To split by the linebreaks usevbLfas delimiter in theSplit()function.
– Pᴇʜ
Nov 28 '18 at 8:32
Have a look here How to Split a single cell into multiple rows and add another row. The technique you will need is very similar. To split by the linebreaks use
vbLf as delimiter in the Split() function.– Pᴇʜ
Nov 28 '18 at 8:32
Have a look here How to Split a single cell into multiple rows and add another row. The technique you will need is very similar. To split by the linebreaks use
vbLf as delimiter in the Split() function.– Pᴇʜ
Nov 28 '18 at 8:32
add a comment |
1 Answer
1
active
oldest
votes
0
Try this.
Sub Test()
Dim vDB, vS, vR(), vHead()
Dim Ws As Worksheet
Dim n As Long, i As Long, j As Integer
Dim r As Long, c As Integer, k As Integer
Dim a As Integer, cnt As Integer
Set Ws = ActiveSheet
Ws.Cells.Replace ".", ""
Ws.Cells.Replace ",", ""
vDB = Ws.UsedRange
r = UBound(vDB, 1)
c = UBound(vDB, 2)
'@@ get colum's number of data
For i = 1 To c
If vDB(2, i) <> "" Then
k = k + 1
ReDim Preserve vHead(1 To k)
vHead(k) = i
End If
Next i
n = 0
'@@ Cycle the cell to see if chr(10) (vbNewline) is included.
For i = 1 To r
If InStr(vDB(i, 1), Chr(10)) Then '~~> if includ chr(10)
vS = Split(vDB(i, 1), Chr(10))
cnt = UBound(vS)
For a = 0 To cnt
n = n + 1
ReDim Preserve vR(1 To c, 1 To n)
For j = 1 To k
vS = Split(vDB(i, vHead(j)), Chr(10))
If j = 1 Then
vR(vHead(j), n) = Split(vS(a))(0)
Else
vR(vHead(j), n) = Val(Trim(vS(a)))
End If
Next j
Next a
Else '~~> if don't include chr(10)
n = n + 1
ReDim Preserve vR(1 To c, 1 To n)
vR(1, n) = vDB(i, 1)
End If
Next i
Sheets.Add
Range("a1").Resize(n, c) = WorksheetFunction.Transpose(vR)
Range("J:O").NumberFormatLocal = "#,###"
End Sub
answered Nov 28 '18 at 13:22
Dy.LeeDy.Lee
3,6721511
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53514372%2fsplit-text-to-rown%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Try this.
Sub Test()
Dim vDB, vS, vR(), vHead()
Dim Ws As Worksheet
Dim n As Long, i As Long, j As Integer
Dim r As Long, c As Integer, k As Integer
Dim a As Integer, cnt As Integer
Set Ws = ActiveSheet
Ws.Cells.Replace ".", ""
Ws.Cells.Replace ",", ""
vDB = Ws.UsedRange
r = UBound(vDB, 1)
c = UBound(vDB, 2)
'@@ get colum's number of data
For i = 1 To c
If vDB(2, i) <> "" Then
k = k + 1
ReDim Preserve vHead(1 To k)
vHead(k) = i
End If
Next i
n = 0
'@@ Cycle the cell to see if chr(10) (vbNewline) is included.
For i = 1 To r
If InStr(vDB(i, 1), Chr(10)) Then '~~> if includ chr(10)
vS = Split(vDB(i, 1), Chr(10))
cnt = UBound(vS)
For a = 0 To cnt
n = n + 1
ReDim Preserve vR(1 To c, 1 To n)
For j = 1 To k
vS = Split(vDB(i, vHead(j)), Chr(10))
If j = 1 Then
vR(vHead(j), n) = Split(vS(a))(0)
Else
vR(vHead(j), n) = Val(Trim(vS(a)))
End If
Next j
Next a
Else '~~> if don't include chr(10)
n = n + 1
ReDim Preserve vR(1 To c, 1 To n)
vR(1, n) = vDB(i, 1)
End If
Next i
Sheets.Add
Range("a1").Resize(n, c) = WorksheetFunction.Transpose(vR)
Range("J:O").NumberFormatLocal = "#,###"
End Sub
answered Nov 28 '18 at 13:22
Dy.LeeDy.Lee
3,6721511
add a comment |
0
Try this.
Sub Test()
Dim vDB, vS, vR(), vHead()
Dim Ws As Worksheet
Dim n As Long, i As Long, j As Integer
Dim r As Long, c As Integer, k As Integer
Dim a As Integer, cnt As Integer
Set Ws = ActiveSheet
Ws.Cells.Replace ".", ""
Ws.Cells.Replace ",", ""
vDB = Ws.UsedRange
r = UBound(vDB, 1)
c = UBound(vDB, 2)
'@@ get colum's number of data
For i = 1 To c
If vDB(2, i) <> "" Then
k = k + 1
ReDim Preserve vHead(1 To k)
vHead(k) = i
End If
Next i
n = 0
'@@ Cycle the cell to see if chr(10) (vbNewline) is included.
For i = 1 To r
If InStr(vDB(i, 1), Chr(10)) Then '~~> if includ chr(10)
vS = Split(vDB(i, 1), Chr(10))
cnt = UBound(vS)
For a = 0 To cnt
n = n + 1
ReDim Preserve vR(1 To c, 1 To n)
For j = 1 To k
vS = Split(vDB(i, vHead(j)), Chr(10))
If j = 1 Then
vR(vHead(j), n) = Split(vS(a))(0)
Else
vR(vHead(j), n) = Val(Trim(vS(a)))
End If
Next j
Next a
Else '~~> if don't include chr(10)
n = n + 1
ReDim Preserve vR(1 To c, 1 To n)
vR(1, n) = vDB(i, 1)
End If
Next i
Sheets.Add
Range("a1").Resize(n, c) = WorksheetFunction.Transpose(vR)
Range("J:O").NumberFormatLocal = "#,###"
End Sub
answered Nov 28 '18 at 13:22
Dy.LeeDy.Lee
3,6721511
add a comment |
0
0
0
Try this.
Sub Test()
Dim vDB, vS, vR(), vHead()
Dim Ws As Worksheet
Dim n As Long, i As Long, j As Integer
Dim r As Long, c As Integer, k As Integer
Dim a As Integer, cnt As Integer
Set Ws = ActiveSheet
Ws.Cells.Replace ".", ""
Ws.Cells.Replace ",", ""
vDB = Ws.UsedRange
r = UBound(vDB, 1)
c = UBound(vDB, 2)
'@@ get colum's number of data
For i = 1 To c
If vDB(2, i) <> "" Then
k = k + 1
ReDim Preserve vHead(1 To k)
vHead(k) = i
End If
Next i
n = 0
'@@ Cycle the cell to see if chr(10) (vbNewline) is included.
For i = 1 To r
If InStr(vDB(i, 1), Chr(10)) Then '~~> if includ chr(10)
vS = Split(vDB(i, 1), Chr(10))
cnt = UBound(vS)
For a = 0 To cnt
n = n + 1
ReDim Preserve vR(1 To c, 1 To n)
For j = 1 To k
vS = Split(vDB(i, vHead(j)), Chr(10))
If j = 1 Then
vR(vHead(j), n) = Split(vS(a))(0)
Else
vR(vHead(j), n) = Val(Trim(vS(a)))
End If
Next j
Next a
Else '~~> if don't include chr(10)
n = n + 1
ReDim Preserve vR(1 To c, 1 To n)
vR(1, n) = vDB(i, 1)
End If
Next i
Sheets.Add
Range("a1").Resize(n, c) = WorksheetFunction.Transpose(vR)
Range("J:O").NumberFormatLocal = "#,###"
End Sub
answered Nov 28 '18 at 13:22
Dy.LeeDy.Lee
3,6721511
Try this.
Sub Test()
Dim vDB, vS, vR(), vHead()
Dim Ws As Worksheet
Dim n As Long, i As Long, j As Integer
Dim r As Long, c As Integer, k As Integer
Dim a As Integer, cnt As Integer
Set Ws = ActiveSheet
Ws.Cells.Replace ".", ""
Ws.Cells.Replace ",", ""
vDB = Ws.UsedRange
r = UBound(vDB, 1)
c = UBound(vDB, 2)
'@@ get colum's number of data
For i = 1 To c
If vDB(2, i) <> "" Then
k = k + 1
ReDim Preserve vHead(1 To k)
vHead(k) = i
End If
Next i
n = 0
'@@ Cycle the cell to see if chr(10) (vbNewline) is included.
For i = 1 To r
If InStr(vDB(i, 1), Chr(10)) Then '~~> if includ chr(10)
vS = Split(vDB(i, 1), Chr(10))
cnt = UBound(vS)
For a = 0 To cnt
n = n + 1
ReDim Preserve vR(1 To c, 1 To n)
For j = 1 To k
vS = Split(vDB(i, vHead(j)), Chr(10))
If j = 1 Then
vR(vHead(j), n) = Split(vS(a))(0)
Else
vR(vHead(j), n) = Val(Trim(vS(a)))
End If
Next j
Next a
Else '~~> if don't include chr(10)
n = n + 1
ReDim Preserve vR(1 To c, 1 To n)
vR(1, n) = vDB(i, 1)
End If
Next i
Sheets.Add
Range("a1").Resize(n, c) = WorksheetFunction.Transpose(vR)
Range("J:O").NumberFormatLocal = "#,###"
End Sub
answered Nov 28 '18 at 13:22
Dy.LeeDy.Lee
3,6721511
edited Nov 28 '18 at 14:36
answered Nov 28 '18 at 13:22
Dy.LeeDy.Lee
3,6721511
answered Nov 28 '18 at 13:22
Dy.LeeDy.Lee
3,6721511
answered Nov 28 '18 at 13:22
Dy.LeeDy.Lee
3,6721511
3,6721511
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53514372%2fsplit-text-to-rown%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Have a look here How to Split a single cell into multiple rows and add another row. The technique you will need is very similar. To split by the linebreaks use
vbLfas delimiter in theSplit()function.– Pᴇʜ
Nov 28 '18 at 8:32