Issues with MID(text, start_num, num_chars) when changing rows
I have created simple test, because I want to create pattern for fast inserting and separating number that is inserted in one place, but when I change row in I3 cell, I want number bellow "Whole number" in I6 cell to be set to zero.
Also I want row 6 to remain intact from column A to E.
Next number inserted inside I6 I want to be separated and placed in row 7 (from column A to E) when I change row on I3 to number 7. Look at the picture I attached. Is there a way to implement this?
excel vba excel-vba excel-formula
edited Nov 26 '18 at 7:25
Pᴇʜ
23.6k62951
asked Nov 24 '18 at 12:53
V.DejanV.Dejan
448
add a comment |
I have created simple test, because I want to create pattern for fast inserting and separating number that is inserted in one place, but when I change row in I3 cell, I want number bellow "Whole number" in I6 cell to be set to zero.
Also I want row 6 to remain intact from column A to E.
Next number inserted inside I6 I want to be separated and placed in row 7 (from column A to E) when I change row on I3 to number 7. Look at the picture I attached. Is there a way to implement this?
excel vba excel-vba excel-formula
edited Nov 26 '18 at 7:25
Pᴇʜ
23.6k62951
asked Nov 24 '18 at 12:53
V.DejanV.Dejan
448
I believe you need vba to do that, if you want to use only formula; it will not work because the link to a cell remain static in formulas, you can only link it with one cell at a time. the cell to be linked can be made dynamic but it will always be one cell.
– usmanhaq
Nov 24 '18 at 13:13
I've added excel-vba to tags, because I have no idea what to do with vba. Hopefully some expert will reply.
– V.Dejan
Nov 24 '18 at 13:25
add a comment |
I have created simple test, because I want to create pattern for fast inserting and separating number that is inserted in one place, but when I change row in I3 cell, I want number bellow "Whole number" in I6 cell to be set to zero.
Also I want row 6 to remain intact from column A to E.
Next number inserted inside I6 I want to be separated and placed in row 7 (from column A to E) when I change row on I3 to number 7. Look at the picture I attached. Is there a way to implement this?
excel vba excel-vba excel-formula
edited Nov 26 '18 at 7:25
Pᴇʜ
23.6k62951
asked Nov 24 '18 at 12:53
V.DejanV.Dejan
448
I have created simple test, because I want to create pattern for fast inserting and separating number that is inserted in one place, but when I change row in I3 cell, I want number bellow "Whole number" in I6 cell to be set to zero.
Also I want row 6 to remain intact from column A to E.
Next number inserted inside I6 I want to be separated and placed in row 7 (from column A to E) when I change row on I3 to number 7. Look at the picture I attached. Is there a way to implement this?
excel vba excel-vba excel-formula
excel vba excel-vba excel-formula
edited Nov 26 '18 at 7:25
Pᴇʜ
23.6k62951
asked Nov 24 '18 at 12:53
V.DejanV.Dejan
448
edited Nov 26 '18 at 7:25
Pᴇʜ
23.6k62951
asked Nov 24 '18 at 12:53
V.DejanV.Dejan
448
edited Nov 26 '18 at 7:25
Pᴇʜ
23.6k62951
edited Nov 26 '18 at 7:25
Pᴇʜ
23.6k62951
edited Nov 26 '18 at 7:25
Pᴇʜ
23.6k62951
23.6k62951
asked Nov 24 '18 at 12:53
V.DejanV.Dejan
448
asked Nov 24 '18 at 12:53
V.DejanV.Dejan
448
asked Nov 24 '18 at 12:53
V.DejanV.Dejan
448
448
I believe you need vba to do that, if you want to use only formula; it will not work because the link to a cell remain static in formulas, you can only link it with one cell at a time. the cell to be linked can be made dynamic but it will always be one cell.
– usmanhaq
Nov 24 '18 at 13:13
I've added excel-vba to tags, because I have no idea what to do with vba. Hopefully some expert will reply.
– V.Dejan
Nov 24 '18 at 13:25
add a comment |
I believe you need vba to do that, if you want to use only formula; it will not work because the link to a cell remain static in formulas, you can only link it with one cell at a time. the cell to be linked can be made dynamic but it will always be one cell.
– usmanhaq
Nov 24 '18 at 13:13
I've added excel-vba to tags, because I have no idea what to do with vba. Hopefully some expert will reply.
– V.Dejan
Nov 24 '18 at 13:25
I believe you need vba to do that, if you want to use only formula; it will not work because the link to a cell remain static in formulas, you can only link it with one cell at a time. the cell to be linked can be made dynamic but it will always be one cell.
– usmanhaq
Nov 24 '18 at 13:13
I believe you need vba to do that, if you want to use only formula; it will not work because the link to a cell remain static in formulas, you can only link it with one cell at a time. the cell to be linked can be made dynamic but it will always be one cell.
– usmanhaq
Nov 24 '18 at 13:13
I've added excel-vba to tags, because I have no idea what to do with vba. Hopefully some expert will reply.
– V.Dejan
Nov 24 '18 at 13:25
I've added excel-vba to tags, because I have no idea what to do with vba. Hopefully some expert will reply.
– V.Dejan
Nov 24 '18 at 13:25
add a comment |
1 Answer
1
active
oldest
votes
Put this function in VBA editor in the sheet where your data is present
For example if your data is in Sheet1 paste this function in Sheet1 of VBA
editor
Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Address = "$I$6") Then
row_num = Range("I3").Value
If Not IsNumeric(row_num) Then Exit Sub
If (row_num < 6) Then Exit Sub
number_value = Target.Value
If Not IsNumeric(number_value) Then Exit Sub
If (number_value < 10000 Or number_value > 99999) Then Exit Sub
Range("A" & row_num).Value = Mid(number_value, 1, 1)
Range("B" & row_num).Value = Mid(number_value, 2, 1)
Range("C" & row_num).Value = Mid(number_value, 3, 1)
Range("D" & row_num).Value = Mid(number_value, 4, 1)
Range("E" & row_num).Value = Mid(number_value, 5, 1)
End If
If (Target.Address = "$I$3") Then
Range("I6").Value = 0
End If
End Sub
Double click Sheet name and paste the function
answered Nov 24 '18 at 13:05
usmanhaqusmanhaq
1,113128
Thanks, I will try and come up with results
– V.Dejan
Nov 24 '18 at 19:52
Thanks m8, works perfect!!!
– V.Dejan
Nov 25 '18 at 16:55
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
});
}
});
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%2f53458352%2fissues-with-midtext-start-num-num-chars-when-changing-rows%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
Put this function in VBA editor in the sheet where your data is present
For example if your data is in Sheet1 paste this function in Sheet1 of VBA
editor
Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Address = "$I$6") Then
row_num = Range("I3").Value
If Not IsNumeric(row_num) Then Exit Sub
If (row_num < 6) Then Exit Sub
number_value = Target.Value
If Not IsNumeric(number_value) Then Exit Sub
If (number_value < 10000 Or number_value > 99999) Then Exit Sub
Range("A" & row_num).Value = Mid(number_value, 1, 1)
Range("B" & row_num).Value = Mid(number_value, 2, 1)
Range("C" & row_num).Value = Mid(number_value, 3, 1)
Range("D" & row_num).Value = Mid(number_value, 4, 1)
Range("E" & row_num).Value = Mid(number_value, 5, 1)
End If
If (Target.Address = "$I$3") Then
Range("I6").Value = 0
End If
End Sub
Double click Sheet name and paste the function
answered Nov 24 '18 at 13:05
usmanhaqusmanhaq
1,113128
Thanks, I will try and come up with results
– V.Dejan
Nov 24 '18 at 19:52
Thanks m8, works perfect!!!
– V.Dejan
Nov 25 '18 at 16:55
add a comment |
Put this function in VBA editor in the sheet where your data is present
For example if your data is in Sheet1 paste this function in Sheet1 of VBA
editor
Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Address = "$I$6") Then
row_num = Range("I3").Value
If Not IsNumeric(row_num) Then Exit Sub
If (row_num < 6) Then Exit Sub
number_value = Target.Value
If Not IsNumeric(number_value) Then Exit Sub
If (number_value < 10000 Or number_value > 99999) Then Exit Sub
Range("A" & row_num).Value = Mid(number_value, 1, 1)
Range("B" & row_num).Value = Mid(number_value, 2, 1)
Range("C" & row_num).Value = Mid(number_value, 3, 1)
Range("D" & row_num).Value = Mid(number_value, 4, 1)
Range("E" & row_num).Value = Mid(number_value, 5, 1)
End If
If (Target.Address = "$I$3") Then
Range("I6").Value = 0
End If
End Sub
Double click Sheet name and paste the function
answered Nov 24 '18 at 13:05
usmanhaqusmanhaq
1,113128
Thanks, I will try and come up with results
– V.Dejan
Nov 24 '18 at 19:52
Thanks m8, works perfect!!!
– V.Dejan
Nov 25 '18 at 16:55
add a comment |
Put this function in VBA editor in the sheet where your data is present
For example if your data is in Sheet1 paste this function in Sheet1 of VBA
editor
Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Address = "$I$6") Then
row_num = Range("I3").Value
If Not IsNumeric(row_num) Then Exit Sub
If (row_num < 6) Then Exit Sub
number_value = Target.Value
If Not IsNumeric(number_value) Then Exit Sub
If (number_value < 10000 Or number_value > 99999) Then Exit Sub
Range("A" & row_num).Value = Mid(number_value, 1, 1)
Range("B" & row_num).Value = Mid(number_value, 2, 1)
Range("C" & row_num).Value = Mid(number_value, 3, 1)
Range("D" & row_num).Value = Mid(number_value, 4, 1)
Range("E" & row_num).Value = Mid(number_value, 5, 1)
End If
If (Target.Address = "$I$3") Then
Range("I6").Value = 0
End If
End Sub
Double click Sheet name and paste the function
answered Nov 24 '18 at 13:05
usmanhaqusmanhaq
1,113128
Put this function in VBA editor in the sheet where your data is present
For example if your data is in Sheet1 paste this function in Sheet1 of VBA
editor
Private Sub Worksheet_Change(ByVal Target As Range)
If (Target.Address = "$I$6") Then
row_num = Range("I3").Value
If Not IsNumeric(row_num) Then Exit Sub
If (row_num < 6) Then Exit Sub
number_value = Target.Value
If Not IsNumeric(number_value) Then Exit Sub
If (number_value < 10000 Or number_value > 99999) Then Exit Sub
Range("A" & row_num).Value = Mid(number_value, 1, 1)
Range("B" & row_num).Value = Mid(number_value, 2, 1)
Range("C" & row_num).Value = Mid(number_value, 3, 1)
Range("D" & row_num).Value = Mid(number_value, 4, 1)
Range("E" & row_num).Value = Mid(number_value, 5, 1)
End If
If (Target.Address = "$I$3") Then
Range("I6").Value = 0
End If
End Sub
Double click Sheet name and paste the function
answered Nov 24 '18 at 13:05
usmanhaqusmanhaq
1,113128
edited Nov 24 '18 at 13:32
answered Nov 24 '18 at 13:05
usmanhaqusmanhaq
1,113128
answered Nov 24 '18 at 13:05
usmanhaqusmanhaq
1,113128
answered Nov 24 '18 at 13:05
usmanhaqusmanhaq
1,113128
1,113128
Thanks, I will try and come up with results
– V.Dejan
Nov 24 '18 at 19:52
Thanks m8, works perfect!!!
– V.Dejan
Nov 25 '18 at 16:55
add a comment |
Thanks, I will try and come up with results
– V.Dejan
Nov 24 '18 at 19:52
Thanks m8, works perfect!!!
– V.Dejan
Nov 25 '18 at 16:55
Thanks, I will try and come up with results
– V.Dejan
Nov 24 '18 at 19:52
Thanks, I will try and come up with results
– V.Dejan
Nov 24 '18 at 19:52
Thanks m8, works perfect!!!
– V.Dejan
Nov 25 '18 at 16:55
Thanks m8, works perfect!!!
– V.Dejan
Nov 25 '18 at 16:55
add a comment |
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.
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%2f53458352%2fissues-with-midtext-start-num-num-chars-when-changing-rows%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
I believe you need vba to do that, if you want to use only formula; it will not work because the link to a cell remain static in formulas, you can only link it with one cell at a time. the cell to be linked can be made dynamic but it will always be one cell.
– usmanhaq
Nov 24 '18 at 13:13
I've added excel-vba to tags, because I have no idea what to do with vba. Hopefully some expert will reply.
– V.Dejan
Nov 24 '18 at 13:25