Remove leading space from columns
I have a spreadsheet with ( 99| 100) in the first cell(A2).
Using =(split(A2," ( | ) ",true)) in cell B2 I am getting 99 and using =CONTINUE(A2, 1, 2) i am getting 100 in cell C2.
There is a leading space in both B2 and C2 how do i remove it, i need to perform a few calculations on B2 and C2.
I've tried using trim but it isn't working.
=trim(split(B248," ( | ) ",true))
For testing purposes i have the following code in cell D2 value(B2) and i get the error Cannot parse text
https://docs.google.com/spreadsheet/ccc?key=0AmZqKT8_r5b_dDJxSnh2Y09ndzhGWm10clp0TXpYT3c&usp=sharing
google-sheets
edited Nov 13 '18 at 5:02
Cœur
17.5k9104145
asked Sep 3 '13 at 15:58
debaldebal
3934827
add a comment |
I have a spreadsheet with ( 99| 100) in the first cell(A2).
Using =(split(A2," ( | ) ",true)) in cell B2 I am getting 99 and using =CONTINUE(A2, 1, 2) i am getting 100 in cell C2.
There is a leading space in both B2 and C2 how do i remove it, i need to perform a few calculations on B2 and C2.
I've tried using trim but it isn't working.
=trim(split(B248," ( | ) ",true))
For testing purposes i have the following code in cell D2 value(B2) and i get the error Cannot parse text
https://docs.google.com/spreadsheet/ccc?key=0AmZqKT8_r5b_dDJxSnh2Y09ndzhGWm10clp0TXpYT3c&usp=sharing
google-sheets
edited Nov 13 '18 at 5:02
Cœur
17.5k9104145
asked Sep 3 '13 at 15:58
debaldebal
3934827
add a comment |
I have a spreadsheet with ( 99| 100) in the first cell(A2).
Using =(split(A2," ( | ) ",true)) in cell B2 I am getting 99 and using =CONTINUE(A2, 1, 2) i am getting 100 in cell C2.
There is a leading space in both B2 and C2 how do i remove it, i need to perform a few calculations on B2 and C2.
I've tried using trim but it isn't working.
=trim(split(B248," ( | ) ",true))
For testing purposes i have the following code in cell D2 value(B2) and i get the error Cannot parse text
https://docs.google.com/spreadsheet/ccc?key=0AmZqKT8_r5b_dDJxSnh2Y09ndzhGWm10clp0TXpYT3c&usp=sharing
google-sheets
edited Nov 13 '18 at 5:02
Cœur
17.5k9104145
asked Sep 3 '13 at 15:58
debaldebal
3934827
I have a spreadsheet with ( 99| 100) in the first cell(A2).
Using =(split(A2," ( | ) ",true)) in cell B2 I am getting 99 and using =CONTINUE(A2, 1, 2) i am getting 100 in cell C2.
There is a leading space in both B2 and C2 how do i remove it, i need to perform a few calculations on B2 and C2.
I've tried using trim but it isn't working.
=trim(split(B248," ( | ) ",true))
For testing purposes i have the following code in cell D2 value(B2) and i get the error Cannot parse text
https://docs.google.com/spreadsheet/ccc?key=0AmZqKT8_r5b_dDJxSnh2Y09ndzhGWm10clp0TXpYT3c&usp=sharing
google-sheets
google-sheets
edited Nov 13 '18 at 5:02
Cœur
17.5k9104145
asked Sep 3 '13 at 15:58
debaldebal
3934827
edited Nov 13 '18 at 5:02
Cœur
17.5k9104145
asked Sep 3 '13 at 15:58
debaldebal
3934827
edited Nov 13 '18 at 5:02
Cœur
17.5k9104145
edited Nov 13 '18 at 5:02
Cœur
17.5k9104145
edited Nov 13 '18 at 5:02
Cœur
17.5k9104145
17.5k9104145
asked Sep 3 '13 at 15:58
debaldebal
3934827
asked Sep 3 '13 at 15:58
debaldebal
3934827
asked Sep 3 '13 at 15:58
debaldebal
3934827
3934827
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
I've tried to use the REGEXREPLACE function to get rid off the white spaces. It turns out that the ASCII character 160 isn't considered to be a white space but a “Non-breaking space”.
Therefore I used the CODE function to reveal the empty space (remove the open bracket first). For your particular setting, the following will work:
=SPLIT(SUBSTITUTE(B1,CHAR(160),""),"(|)")
answered Sep 3 '13 at 20:02
Jacob Jan TuinstraJacob Jan Tuinstra
89111539
1
Jacob, that works, but please could you explain what was I doing wrong there and how you went about it?
– debal
Sep 4 '13 at 14:36
1
@debal See my revised answer.
– Jacob Jan Tuinstra
Sep 5 '13 at 12:25
add a comment |
I did a quick test and was able to get A2 and B2 to add without a problem. Please see:
https://docs.google.com/spreadsheet/ccc?key=0AvRseCY0-3SVdEJybVU5U1pZYzdxX3NzNEVOekxFOVE&usp=sharing
Perhaps there is some column formatting on your values?
answered Sep 3 '13 at 16:03
bakercpbakercp
859711
please check the sheet that i pasted
– debal
Sep 3 '13 at 16:15
I just noticed that the space in your first example is actually a non-breaking space (fileformat.info/info/unicode/char/a0/index.htm), not a standard space (fileformat.info/info/unicode/char/0020/index.htm).
– bakercp
Sep 4 '13 at 3:07
I tried several regex-based solutions and Jacob's is the best I've found.
– bakercp
Sep 4 '13 at 3:32
@bakercp tried that myself as well, haha, without success
– Jacob Jan Tuinstra
Sep 4 '13 at 8:06
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%2f18596651%2fremove-leading-space-from-columns%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
I've tried to use the REGEXREPLACE function to get rid off the white spaces. It turns out that the ASCII character 160 isn't considered to be a white space but a “Non-breaking space”.
Therefore I used the CODE function to reveal the empty space (remove the open bracket first). For your particular setting, the following will work:
=SPLIT(SUBSTITUTE(B1,CHAR(160),""),"(|)")
answered Sep 3 '13 at 20:02
Jacob Jan TuinstraJacob Jan Tuinstra
89111539
1
Jacob, that works, but please could you explain what was I doing wrong there and how you went about it?
– debal
Sep 4 '13 at 14:36
1
@debal See my revised answer.
– Jacob Jan Tuinstra
Sep 5 '13 at 12:25
add a comment |
I've tried to use the REGEXREPLACE function to get rid off the white spaces. It turns out that the ASCII character 160 isn't considered to be a white space but a “Non-breaking space”.
Therefore I used the CODE function to reveal the empty space (remove the open bracket first). For your particular setting, the following will work:
=SPLIT(SUBSTITUTE(B1,CHAR(160),""),"(|)")
answered Sep 3 '13 at 20:02
Jacob Jan TuinstraJacob Jan Tuinstra
89111539
1
Jacob, that works, but please could you explain what was I doing wrong there and how you went about it?
– debal
Sep 4 '13 at 14:36
1
@debal See my revised answer.
– Jacob Jan Tuinstra
Sep 5 '13 at 12:25
add a comment |
I've tried to use the REGEXREPLACE function to get rid off the white spaces. It turns out that the ASCII character 160 isn't considered to be a white space but a “Non-breaking space”.
Therefore I used the CODE function to reveal the empty space (remove the open bracket first). For your particular setting, the following will work:
=SPLIT(SUBSTITUTE(B1,CHAR(160),""),"(|)")
answered Sep 3 '13 at 20:02
Jacob Jan TuinstraJacob Jan Tuinstra
89111539
I've tried to use the REGEXREPLACE function to get rid off the white spaces. It turns out that the ASCII character 160 isn't considered to be a white space but a “Non-breaking space”.
Therefore I used the CODE function to reveal the empty space (remove the open bracket first). For your particular setting, the following will work:
=SPLIT(SUBSTITUTE(B1,CHAR(160),""),"(|)")
answered Sep 3 '13 at 20:02
Jacob Jan TuinstraJacob Jan Tuinstra
89111539
edited Sep 5 '13 at 12:35
answered Sep 3 '13 at 20:02
Jacob Jan TuinstraJacob Jan Tuinstra
89111539
answered Sep 3 '13 at 20:02
Jacob Jan TuinstraJacob Jan Tuinstra
89111539
answered Sep 3 '13 at 20:02
Jacob Jan TuinstraJacob Jan Tuinstra
89111539
89111539
1
Jacob, that works, but please could you explain what was I doing wrong there and how you went about it?
– debal
Sep 4 '13 at 14:36
1
@debal See my revised answer.
– Jacob Jan Tuinstra
Sep 5 '13 at 12:25
add a comment |
1
Jacob, that works, but please could you explain what was I doing wrong there and how you went about it?
– debal
Sep 4 '13 at 14:36
1
@debal See my revised answer.
– Jacob Jan Tuinstra
Sep 5 '13 at 12:25
1
1
Jacob, that works, but please could you explain what was I doing wrong there and how you went about it?
– debal
Sep 4 '13 at 14:36
Jacob, that works, but please could you explain what was I doing wrong there and how you went about it?
– debal
Sep 4 '13 at 14:36
1
1
@debal See my revised answer.
– Jacob Jan Tuinstra
Sep 5 '13 at 12:25
@debal See my revised answer.
– Jacob Jan Tuinstra
Sep 5 '13 at 12:25
add a comment |
I did a quick test and was able to get A2 and B2 to add without a problem. Please see:
https://docs.google.com/spreadsheet/ccc?key=0AvRseCY0-3SVdEJybVU5U1pZYzdxX3NzNEVOekxFOVE&usp=sharing
Perhaps there is some column formatting on your values?
answered Sep 3 '13 at 16:03
bakercpbakercp
859711
please check the sheet that i pasted
– debal
Sep 3 '13 at 16:15
I just noticed that the space in your first example is actually a non-breaking space (fileformat.info/info/unicode/char/a0/index.htm), not a standard space (fileformat.info/info/unicode/char/0020/index.htm).
– bakercp
Sep 4 '13 at 3:07
I tried several regex-based solutions and Jacob's is the best I've found.
– bakercp
Sep 4 '13 at 3:32
@bakercp tried that myself as well, haha, without success
– Jacob Jan Tuinstra
Sep 4 '13 at 8:06
add a comment |
I did a quick test and was able to get A2 and B2 to add without a problem. Please see:
https://docs.google.com/spreadsheet/ccc?key=0AvRseCY0-3SVdEJybVU5U1pZYzdxX3NzNEVOekxFOVE&usp=sharing
Perhaps there is some column formatting on your values?
answered Sep 3 '13 at 16:03
bakercpbakercp
859711
please check the sheet that i pasted
– debal
Sep 3 '13 at 16:15
I just noticed that the space in your first example is actually a non-breaking space (fileformat.info/info/unicode/char/a0/index.htm), not a standard space (fileformat.info/info/unicode/char/0020/index.htm).
– bakercp
Sep 4 '13 at 3:07
I tried several regex-based solutions and Jacob's is the best I've found.
– bakercp
Sep 4 '13 at 3:32
@bakercp tried that myself as well, haha, without success
– Jacob Jan Tuinstra
Sep 4 '13 at 8:06
add a comment |
I did a quick test and was able to get A2 and B2 to add without a problem. Please see:
https://docs.google.com/spreadsheet/ccc?key=0AvRseCY0-3SVdEJybVU5U1pZYzdxX3NzNEVOekxFOVE&usp=sharing
Perhaps there is some column formatting on your values?
answered Sep 3 '13 at 16:03
bakercpbakercp
859711
I did a quick test and was able to get A2 and B2 to add without a problem. Please see:
https://docs.google.com/spreadsheet/ccc?key=0AvRseCY0-3SVdEJybVU5U1pZYzdxX3NzNEVOekxFOVE&usp=sharing
Perhaps there is some column formatting on your values?
answered Sep 3 '13 at 16:03
bakercpbakercp
859711
answered Sep 3 '13 at 16:03
bakercpbakercp
859711
answered Sep 3 '13 at 16:03
bakercpbakercp
859711
answered Sep 3 '13 at 16:03
bakercpbakercp
859711
859711
please check the sheet that i pasted
– debal
Sep 3 '13 at 16:15
I just noticed that the space in your first example is actually a non-breaking space (fileformat.info/info/unicode/char/a0/index.htm), not a standard space (fileformat.info/info/unicode/char/0020/index.htm).
– bakercp
Sep 4 '13 at 3:07
I tried several regex-based solutions and Jacob's is the best I've found.
– bakercp
Sep 4 '13 at 3:32
@bakercp tried that myself as well, haha, without success
– Jacob Jan Tuinstra
Sep 4 '13 at 8:06
add a comment |
please check the sheet that i pasted
– debal
Sep 3 '13 at 16:15
I just noticed that the space in your first example is actually a non-breaking space (fileformat.info/info/unicode/char/a0/index.htm), not a standard space (fileformat.info/info/unicode/char/0020/index.htm).
– bakercp
Sep 4 '13 at 3:07
I tried several regex-based solutions and Jacob's is the best I've found.
– bakercp
Sep 4 '13 at 3:32
@bakercp tried that myself as well, haha, without success
– Jacob Jan Tuinstra
Sep 4 '13 at 8:06
please check the sheet that i pasted
– debal
Sep 3 '13 at 16:15
please check the sheet that i pasted
– debal
Sep 3 '13 at 16:15
I just noticed that the space in your first example is actually a non-breaking space (fileformat.info/info/unicode/char/a0/index.htm), not a standard space (fileformat.info/info/unicode/char/0020/index.htm).
– bakercp
Sep 4 '13 at 3:07
I just noticed that the space in your first example is actually a non-breaking space (fileformat.info/info/unicode/char/a0/index.htm), not a standard space (fileformat.info/info/unicode/char/0020/index.htm).
– bakercp
Sep 4 '13 at 3:07
I tried several regex-based solutions and Jacob's is the best I've found.
– bakercp
Sep 4 '13 at 3:32
I tried several regex-based solutions and Jacob's is the best I've found.
– bakercp
Sep 4 '13 at 3:32
@bakercp tried that myself as well, haha, without success
– Jacob Jan Tuinstra
Sep 4 '13 at 8:06
@bakercp tried that myself as well, haha, without success
– Jacob Jan Tuinstra
Sep 4 '13 at 8:06
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%2f18596651%2fremove-leading-space-from-columns%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
