How to convert text to decimal in openoffice calc?
I have a column which contains:
€ 53,28
€ 1.336,0
€ 69,90
€ 296,50
€ 899,00
€ 149,90
€ 697,40
and so on. I want to sum the column up, but for openoffice it is text and not a decimal.
What I did:
=SUBSTITUTE(F7,".","") to get rid of the the dot
|€ 1336,0|
=SUBSTITUTE(G7,"€","") to get rid of the euro symbol
| 1336,0|
=RIGHT(H7, LEN(H7)-1) to get rid of the blank
|1336,0|
=SUBSTITUTE(I7,",",".") to change the comma to dots
|1336.0|
and after that I copy the whole column and paste it as "unformatted text" only after these steps I get a number with which openoffice can work.
Isn't there an easier way???
conversion text-editing openoffice-calc
asked Dec 29 '10 at 13:08
wurlogwurlog
1181415
add a comment |
I have a column which contains:
€ 53,28
€ 1.336,0
€ 69,90
€ 296,50
€ 899,00
€ 149,90
€ 697,40
and so on. I want to sum the column up, but for openoffice it is text and not a decimal.
What I did:
=SUBSTITUTE(F7,".","") to get rid of the the dot
|€ 1336,0|
=SUBSTITUTE(G7,"€","") to get rid of the euro symbol
| 1336,0|
=RIGHT(H7, LEN(H7)-1) to get rid of the blank
|1336,0|
=SUBSTITUTE(I7,",",".") to change the comma to dots
|1336.0|
and after that I copy the whole column and paste it as "unformatted text" only after these steps I get a number with which openoffice can work.
Isn't there an easier way???
conversion text-editing openoffice-calc
asked Dec 29 '10 at 13:08
wurlogwurlog
1181415
add a comment |
I have a column which contains:
€ 53,28
€ 1.336,0
€ 69,90
€ 296,50
€ 899,00
€ 149,90
€ 697,40
and so on. I want to sum the column up, but for openoffice it is text and not a decimal.
What I did:
=SUBSTITUTE(F7,".","") to get rid of the the dot
|€ 1336,0|
=SUBSTITUTE(G7,"€","") to get rid of the euro symbol
| 1336,0|
=RIGHT(H7, LEN(H7)-1) to get rid of the blank
|1336,0|
=SUBSTITUTE(I7,",",".") to change the comma to dots
|1336.0|
and after that I copy the whole column and paste it as "unformatted text" only after these steps I get a number with which openoffice can work.
Isn't there an easier way???
conversion text-editing openoffice-calc
asked Dec 29 '10 at 13:08
wurlogwurlog
1181415
I have a column which contains:
€ 53,28
€ 1.336,0
€ 69,90
€ 296,50
€ 899,00
€ 149,90
€ 697,40
and so on. I want to sum the column up, but for openoffice it is text and not a decimal.
What I did:
=SUBSTITUTE(F7,".","") to get rid of the the dot
|€ 1336,0|
=SUBSTITUTE(G7,"€","") to get rid of the euro symbol
| 1336,0|
=RIGHT(H7, LEN(H7)-1) to get rid of the blank
|1336,0|
=SUBSTITUTE(I7,",",".") to change the comma to dots
|1336.0|
and after that I copy the whole column and paste it as "unformatted text" only after these steps I get a number with which openoffice can work.
Isn't there an easier way???
conversion text-editing openoffice-calc
conversion text-editing openoffice-calc
asked Dec 29 '10 at 13:08
wurlogwurlog
1181415
asked Dec 29 '10 at 13:08
wurlogwurlog
1181415
asked Dec 29 '10 at 13:08
wurlogwurlog
1181415
asked Dec 29 '10 at 13:08
wurlogwurlog
1181415
asked Dec 29 '10 at 13:08
wurlogwurlog
1181415
1181415
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
First, you can combine all your formulas into one:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"€ ";"");".";"");",";".")
Then add the VALUE formula to the mix (Returns a number, given a text representation):
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"€ ";"");".";"");",";"."))
Calc should now recognize the resultant cells of this formula as numbers.
answered Dec 29 '10 at 16:10
Siim KSiim K
5,93963965
+1 forVALUE. For simpler cases, that alone is enough.
– matt
Mar 13 '16 at 20:17
This formula incorrectly parses'1,091.00into1.09(well I can see that was the original OP's request, but just giving hands up to people who have a bit different use case)
– Dimitry K
May 16 '17 at 9:27
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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%2fsuperuser.com%2fquestions%2f226883%2fhow-to-convert-text-to-decimal-in-openoffice-calc%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
First, you can combine all your formulas into one:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"€ ";"");".";"");",";".")
Then add the VALUE formula to the mix (Returns a number, given a text representation):
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"€ ";"");".";"");",";"."))
Calc should now recognize the resultant cells of this formula as numbers.
answered Dec 29 '10 at 16:10
Siim KSiim K
5,93963965
+1 forVALUE. For simpler cases, that alone is enough.
– matt
Mar 13 '16 at 20:17
This formula incorrectly parses'1,091.00into1.09(well I can see that was the original OP's request, but just giving hands up to people who have a bit different use case)
– Dimitry K
May 16 '17 at 9:27
add a comment |
First, you can combine all your formulas into one:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"€ ";"");".";"");",";".")
Then add the VALUE formula to the mix (Returns a number, given a text representation):
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"€ ";"");".";"");",";"."))
Calc should now recognize the resultant cells of this formula as numbers.
answered Dec 29 '10 at 16:10
Siim KSiim K
5,93963965
+1 forVALUE. For simpler cases, that alone is enough.
– matt
Mar 13 '16 at 20:17
This formula incorrectly parses'1,091.00into1.09(well I can see that was the original OP's request, but just giving hands up to people who have a bit different use case)
– Dimitry K
May 16 '17 at 9:27
add a comment |
First, you can combine all your formulas into one:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"€ ";"");".";"");",";".")
Then add the VALUE formula to the mix (Returns a number, given a text representation):
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"€ ";"");".";"");",";"."))
Calc should now recognize the resultant cells of this formula as numbers.
answered Dec 29 '10 at 16:10
Siim KSiim K
5,93963965
First, you can combine all your formulas into one:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"€ ";"");".";"");",";".")
Then add the VALUE formula to the mix (Returns a number, given a text representation):
=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"€ ";"");".";"");",";"."))
Calc should now recognize the resultant cells of this formula as numbers.
answered Dec 29 '10 at 16:10
Siim KSiim K
5,93963965
answered Dec 29 '10 at 16:10
Siim KSiim K
5,93963965
answered Dec 29 '10 at 16:10
Siim KSiim K
5,93963965
answered Dec 29 '10 at 16:10
Siim KSiim K
5,93963965
5,93963965
+1 forVALUE. For simpler cases, that alone is enough.
– matt
Mar 13 '16 at 20:17
This formula incorrectly parses'1,091.00into1.09(well I can see that was the original OP's request, but just giving hands up to people who have a bit different use case)
– Dimitry K
May 16 '17 at 9:27
add a comment |
+1 forVALUE. For simpler cases, that alone is enough.
– matt
Mar 13 '16 at 20:17
This formula incorrectly parses'1,091.00into1.09(well I can see that was the original OP's request, but just giving hands up to people who have a bit different use case)
– Dimitry K
May 16 '17 at 9:27
+1 for
VALUE. For simpler cases, that alone is enough.– matt
Mar 13 '16 at 20:17
+1 for
VALUE. For simpler cases, that alone is enough.– matt
Mar 13 '16 at 20:17
This formula incorrectly parses
'1,091.00 into 1.09 (well I can see that was the original OP's request, but just giving hands up to people who have a bit different use case)– Dimitry K
May 16 '17 at 9:27
This formula incorrectly parses
'1,091.00 into 1.09 (well I can see that was the original OP's request, but just giving hands up to people who have a bit different use case)– Dimitry K
May 16 '17 at 9:27
add a comment |
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f226883%2fhow-to-convert-text-to-decimal-in-openoffice-calc%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
