Convert numbers from Scientific Notation to text
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
-2
I tried so many ways (.numberformats, etc.), I cannot manage to convert those scientific numbers (column [A]) into text, so that the EAN Code is shown properly (column [B]).
I need a VBA solution.
any suggestions?
edit: solution which works for me, but I am not happy with:
For i = 1 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Range("A" & i).NumberFormat = "@"
Range("A" & i) = Trim(Range("A" & i))
Next i
This will directly convert Column [A] into the right format.
excel vba excel-vba
asked Nov 16 '18 at 15:12
smartinismartini
1087
add a comment |
-2
I tried so many ways (.numberformats, etc.), I cannot manage to convert those scientific numbers (column [A]) into text, so that the EAN Code is shown properly (column [B]).
I need a VBA solution.
any suggestions?
edit: solution which works for me, but I am not happy with:
For i = 1 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Range("A" & i).NumberFormat = "@"
Range("A" & i) = Trim(Range("A" & i))
Next i
This will directly convert Column [A] into the right format.
excel vba excel-vba
asked Nov 16 '18 at 15:12
smartinismartini
1087
Non VBA solution = Format > Custom > 0
– urdearboy
Nov 16 '18 at 15:16
1
Hello and welcome to Stack Overflow! Please take the tour and read through the help center to learn what we'll do to help you. TL;DR: this isn't a code writing service so you'll have to provide your best shot at it and indicate where you're stuck, then someone will help you fix that specific issue.
– FreeMan
Nov 16 '18 at 15:23
Thanks, I know that rule and tried to tell that coding like ".numberformats.etc" won't work. so I did not paste my not working code in here...
– smartini
Nov 16 '18 at 15:25
What about usingTEXT? InB2put=TEXT(A2,"#")
– Storax
Nov 16 '18 at 18:53
add a comment |
-2
-2
-2
I tried so many ways (.numberformats, etc.), I cannot manage to convert those scientific numbers (column [A]) into text, so that the EAN Code is shown properly (column [B]).
I need a VBA solution.
any suggestions?
edit: solution which works for me, but I am not happy with:
For i = 1 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Range("A" & i).NumberFormat = "@"
Range("A" & i) = Trim(Range("A" & i))
Next i
This will directly convert Column [A] into the right format.
excel vba excel-vba
asked Nov 16 '18 at 15:12
smartinismartini
1087
I tried so many ways (.numberformats, etc.), I cannot manage to convert those scientific numbers (column [A]) into text, so that the EAN Code is shown properly (column [B]).
I need a VBA solution.
any suggestions?
edit: solution which works for me, but I am not happy with:
For i = 1 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Range("A" & i).NumberFormat = "@"
Range("A" & i) = Trim(Range("A" & i))
Next i
This will directly convert Column [A] into the right format.
excel vba excel-vba
excel vba excel-vba
asked Nov 16 '18 at 15:12
smartinismartini
1087
asked Nov 16 '18 at 15:12
smartinismartini
1087
edited Nov 16 '18 at 15:28
smartini
asked Nov 16 '18 at 15:12
smartinismartini
1087
asked Nov 16 '18 at 15:12
smartinismartini
1087
asked Nov 16 '18 at 15:12
smartinismartini
1087
1087
Non VBA solution = Format > Custom > 0
– urdearboy
Nov 16 '18 at 15:16
1
Hello and welcome to Stack Overflow! Please take the tour and read through the help center to learn what we'll do to help you. TL;DR: this isn't a code writing service so you'll have to provide your best shot at it and indicate where you're stuck, then someone will help you fix that specific issue.
– FreeMan
Nov 16 '18 at 15:23
Thanks, I know that rule and tried to tell that coding like ".numberformats.etc" won't work. so I did not paste my not working code in here...
– smartini
Nov 16 '18 at 15:25
What about usingTEXT? InB2put=TEXT(A2,"#")
– Storax
Nov 16 '18 at 18:53
add a comment |
Non VBA solution = Format > Custom > 0
– urdearboy
Nov 16 '18 at 15:16
1
Hello and welcome to Stack Overflow! Please take the tour and read through the help center to learn what we'll do to help you. TL;DR: this isn't a code writing service so you'll have to provide your best shot at it and indicate where you're stuck, then someone will help you fix that specific issue.
– FreeMan
Nov 16 '18 at 15:23
Thanks, I know that rule and tried to tell that coding like ".numberformats.etc" won't work. so I did not paste my not working code in here...
– smartini
Nov 16 '18 at 15:25
What about usingTEXT? InB2put=TEXT(A2,"#")
– Storax
Nov 16 '18 at 18:53
Non VBA solution = Format > Custom > 0
– urdearboy
Nov 16 '18 at 15:16
Non VBA solution = Format > Custom > 0
– urdearboy
Nov 16 '18 at 15:16
1
1
Hello and welcome to Stack Overflow! Please take the tour and read through the help center to learn what we'll do to help you. TL;DR: this isn't a code writing service so you'll have to provide your best shot at it and indicate where you're stuck, then someone will help you fix that specific issue.
– FreeMan
Nov 16 '18 at 15:23
Hello and welcome to Stack Overflow! Please take the tour and read through the help center to learn what we'll do to help you. TL;DR: this isn't a code writing service so you'll have to provide your best shot at it and indicate where you're stuck, then someone will help you fix that specific issue.
– FreeMan
Nov 16 '18 at 15:23
Thanks, I know that rule and tried to tell that coding like ".numberformats.etc" won't work. so I did not paste my not working code in here...
– smartini
Nov 16 '18 at 15:25
Thanks, I know that rule and tried to tell that coding like ".numberformats.etc" won't work. so I did not paste my not working code in here...
– smartini
Nov 16 '18 at 15:25
What about using
TEXT? In B2 put =TEXT(A2,"#")– Storax
Nov 16 '18 at 18:53
What about using
TEXT? In B2 put =TEXT(A2,"#")– Storax
Nov 16 '18 at 18:53
add a comment |
2 Answers
2
active
oldest
votes
1
Dim iRowCount As Integer
iRowCount = Cells(Rows.Count, "A").End(xlUp).Row
Range("B2:B" & iRowCount).Value = Range("A2:A" & iRowCount).Value
Range("B2:B" & iRowCount).NumberFormat = "0"
answered Nov 16 '18 at 22:04
Michal RosaMichal Rosa
1,3561815
Just a note, for large sets of data, it'd be better to declareiRowCountas along.Integervalues have a max of32,767whereaslongshave a max of2,147,483,647which will cover the max number of rows in a sheet which is1,048,576
– Jchang43
Nov 16 '18 at 23:20
add a comment |
0
Try this:
Sub FFF()
Range("B2").Value = "" & Range("A2").Value
End Sub
answered Nov 16 '18 at 19:50
JohnyLJohnyL
3,73811025
no, did not work.
– smartini
Nov 16 '18 at 21:44
@smartini For me it did work.
– JohnyL
Nov 17 '18 at 7:34
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%2f53340548%2fconvert-numbers-from-scientific-notation-to-text%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Dim iRowCount As Integer
iRowCount = Cells(Rows.Count, "A").End(xlUp).Row
Range("B2:B" & iRowCount).Value = Range("A2:A" & iRowCount).Value
Range("B2:B" & iRowCount).NumberFormat = "0"
answered Nov 16 '18 at 22:04
Michal RosaMichal Rosa
1,3561815
Just a note, for large sets of data, it'd be better to declareiRowCountas along.Integervalues have a max of32,767whereaslongshave a max of2,147,483,647which will cover the max number of rows in a sheet which is1,048,576
– Jchang43
Nov 16 '18 at 23:20
add a comment |
1
Dim iRowCount As Integer
iRowCount = Cells(Rows.Count, "A").End(xlUp).Row
Range("B2:B" & iRowCount).Value = Range("A2:A" & iRowCount).Value
Range("B2:B" & iRowCount).NumberFormat = "0"
answered Nov 16 '18 at 22:04
Michal RosaMichal Rosa
1,3561815
Just a note, for large sets of data, it'd be better to declareiRowCountas along.Integervalues have a max of32,767whereaslongshave a max of2,147,483,647which will cover the max number of rows in a sheet which is1,048,576
– Jchang43
Nov 16 '18 at 23:20
add a comment |
1
1
1
Dim iRowCount As Integer
iRowCount = Cells(Rows.Count, "A").End(xlUp).Row
Range("B2:B" & iRowCount).Value = Range("A2:A" & iRowCount).Value
Range("B2:B" & iRowCount).NumberFormat = "0"
answered Nov 16 '18 at 22:04
Michal RosaMichal Rosa
1,3561815
Dim iRowCount As Integer
iRowCount = Cells(Rows.Count, "A").End(xlUp).Row
Range("B2:B" & iRowCount).Value = Range("A2:A" & iRowCount).Value
Range("B2:B" & iRowCount).NumberFormat = "0"
answered Nov 16 '18 at 22:04
Michal RosaMichal Rosa
1,3561815
answered Nov 16 '18 at 22:04
Michal RosaMichal Rosa
1,3561815
answered Nov 16 '18 at 22:04
Michal RosaMichal Rosa
1,3561815
answered Nov 16 '18 at 22:04
Michal RosaMichal Rosa
1,3561815
1,3561815
Just a note, for large sets of data, it'd be better to declareiRowCountas along.Integervalues have a max of32,767whereaslongshave a max of2,147,483,647which will cover the max number of rows in a sheet which is1,048,576
– Jchang43
Nov 16 '18 at 23:20
add a comment |
Just a note, for large sets of data, it'd be better to declareiRowCountas along.Integervalues have a max of32,767whereaslongshave a max of2,147,483,647which will cover the max number of rows in a sheet which is1,048,576
– Jchang43
Nov 16 '18 at 23:20
Just a note, for large sets of data, it'd be better to declare
iRowCount as a long. Integer values have a max of 32,767 whereas longs have a max of 2,147,483,647 which will cover the max number of rows in a sheet which is 1,048,576– Jchang43
Nov 16 '18 at 23:20
Just a note, for large sets of data, it'd be better to declare
iRowCount as a long. Integer values have a max of 32,767 whereas longs have a max of 2,147,483,647 which will cover the max number of rows in a sheet which is 1,048,576– Jchang43
Nov 16 '18 at 23:20
add a comment |
0
Try this:
Sub FFF()
Range("B2").Value = "" & Range("A2").Value
End Sub
answered Nov 16 '18 at 19:50
JohnyLJohnyL
3,73811025
no, did not work.
– smartini
Nov 16 '18 at 21:44
@smartini For me it did work.
– JohnyL
Nov 17 '18 at 7:34
add a comment |
0
Try this:
Sub FFF()
Range("B2").Value = "" & Range("A2").Value
End Sub
answered Nov 16 '18 at 19:50
JohnyLJohnyL
3,73811025
no, did not work.
– smartini
Nov 16 '18 at 21:44
@smartini For me it did work.
– JohnyL
Nov 17 '18 at 7:34
add a comment |
0
0
0
Try this:
Sub FFF()
Range("B2").Value = "" & Range("A2").Value
End Sub
answered Nov 16 '18 at 19:50
JohnyLJohnyL
3,73811025
Try this:
Sub FFF()
Range("B2").Value = "" & Range("A2").Value
End Sub
answered Nov 16 '18 at 19:50
JohnyLJohnyL
3,73811025
answered Nov 16 '18 at 19:50
JohnyLJohnyL
3,73811025
answered Nov 16 '18 at 19:50
JohnyLJohnyL
3,73811025
answered Nov 16 '18 at 19:50
JohnyLJohnyL
3,73811025
3,73811025
no, did not work.
– smartini
Nov 16 '18 at 21:44
@smartini For me it did work.
– JohnyL
Nov 17 '18 at 7:34
add a comment |
no, did not work.
– smartini
Nov 16 '18 at 21:44
@smartini For me it did work.
– JohnyL
Nov 17 '18 at 7:34
no, did not work.
– smartini
Nov 16 '18 at 21:44
no, did not work.
– smartini
Nov 16 '18 at 21:44
@smartini For me it did work.
– JohnyL
Nov 17 '18 at 7:34
@smartini For me it did work.
– JohnyL
Nov 17 '18 at 7:34
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%2f53340548%2fconvert-numbers-from-scientific-notation-to-text%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
Non VBA solution = Format > Custom > 0
– urdearboy
Nov 16 '18 at 15:16
1
Hello and welcome to Stack Overflow! Please take the tour and read through the help center to learn what we'll do to help you. TL;DR: this isn't a code writing service so you'll have to provide your best shot at it and indicate where you're stuck, then someone will help you fix that specific issue.
– FreeMan
Nov 16 '18 at 15:23
Thanks, I know that rule and tried to tell that coding like ".numberformats.etc" won't work. so I did not paste my not working code in here...
– smartini
Nov 16 '18 at 15:25
What about using
TEXT? InB2put=TEXT(A2,"#")– Storax
Nov 16 '18 at 18:53