Format telephone numbers in Excel
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have a column of numbers in the format
'+43 ??? ?? ??
(the ' is there so Excel doesn't think it's a formula and ? represents an integer)
which I would like to have in the format
'+43 ?? ??? ??
(having the block with 3 integers in the middle instead of having it after the '+43)
microsoft-excel formatting
asked Mar 6 at 15:35
ErnestoErnesto
183
add a comment |
I have a column of numbers in the format
'+43 ??? ?? ??
(the ' is there so Excel doesn't think it's a formula and ? represents an integer)
which I would like to have in the format
'+43 ?? ??? ??
(having the block with 3 integers in the middle instead of having it after the '+43)
microsoft-excel formatting
asked Mar 6 at 15:35
ErnestoErnesto
183
1
Easiest thing to do would be to copy the column data out to Notepad++, use some regex to change the pattern, then paste it all back into Excel. As for the actual regex, i can't help with that.
– spikey_richie
Mar 6 at 15:39
add a comment |
I have a column of numbers in the format
'+43 ??? ?? ??
(the ' is there so Excel doesn't think it's a formula and ? represents an integer)
which I would like to have in the format
'+43 ?? ??? ??
(having the block with 3 integers in the middle instead of having it after the '+43)
microsoft-excel formatting
asked Mar 6 at 15:35
ErnestoErnesto
183
I have a column of numbers in the format
'+43 ??? ?? ??
(the ' is there so Excel doesn't think it's a formula and ? represents an integer)
which I would like to have in the format
'+43 ?? ??? ??
(having the block with 3 integers in the middle instead of having it after the '+43)
microsoft-excel formatting
microsoft-excel formatting
asked Mar 6 at 15:35
ErnestoErnesto
183
asked Mar 6 at 15:35
ErnestoErnesto
183
edited Mar 6 at 15:38
Ernesto
asked Mar 6 at 15:35
ErnestoErnesto
183
asked Mar 6 at 15:35
ErnestoErnesto
183
asked Mar 6 at 15:35
ErnestoErnesto
183
183
1
Easiest thing to do would be to copy the column data out to Notepad++, use some regex to change the pattern, then paste it all back into Excel. As for the actual regex, i can't help with that.
– spikey_richie
Mar 6 at 15:39
add a comment |
1
Easiest thing to do would be to copy the column data out to Notepad++, use some regex to change the pattern, then paste it all back into Excel. As for the actual regex, i can't help with that.
– spikey_richie
Mar 6 at 15:39
1
1
Easiest thing to do would be to copy the column data out to Notepad++, use some regex to change the pattern, then paste it all back into Excel. As for the actual regex, i can't help with that.
– spikey_richie
Mar 6 at 15:39
Easiest thing to do would be to copy the column data out to Notepad++, use some regex to change the pattern, then paste it all back into Excel. As for the actual regex, i can't help with that.
– spikey_richie
Mar 6 at 15:39
add a comment |
2 Answers
2
active
oldest
votes
I use Excel RegEx Find/Replace add-in, and with that you can use this formula to change format:=RegExReplace(A1,"(+43 d{2})(d) ","$1 $2")
You can see here how the regular expression part works: https://regex101.com/r/ccLkRK/1
answered Mar 6 at 15:45
Máté JuhászMáté Juhász
14.8k63552
thank you very much!
– Ernesto
Mar 6 at 16:02
1
If you are using Excel 2013 or later, or Excel 365, you can use flash fill. No add-ins necessary at all.
– Vaibhav Garg
Mar 12 at 12:02
add a comment |
I assume that you are using a current version of Excel (2013+ or 365). In an adjacent column, next to the input, just type out a few outputs by hand. Excel should infer the pattern and offer to Flash Fill automatically. Just press Enter, and Done!
If it does not, you can click on Fill on the Home tab, and select Flash Fill.
No Add-ins necessary.
As seen in the screenshot, 2 examples was all it took.
answered Mar 12 at 11:43
Vaibhav GargVaibhav Garg
9312
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%2f1411836%2fformat-telephone-numbers-in-excel%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 use Excel RegEx Find/Replace add-in, and with that you can use this formula to change format:=RegExReplace(A1,"(+43 d{2})(d) ","$1 $2")
You can see here how the regular expression part works: https://regex101.com/r/ccLkRK/1
answered Mar 6 at 15:45
Máté JuhászMáté Juhász
14.8k63552
thank you very much!
– Ernesto
Mar 6 at 16:02
1
If you are using Excel 2013 or later, or Excel 365, you can use flash fill. No add-ins necessary at all.
– Vaibhav Garg
Mar 12 at 12:02
add a comment |
I use Excel RegEx Find/Replace add-in, and with that you can use this formula to change format:=RegExReplace(A1,"(+43 d{2})(d) ","$1 $2")
You can see here how the regular expression part works: https://regex101.com/r/ccLkRK/1
answered Mar 6 at 15:45
Máté JuhászMáté Juhász
14.8k63552
thank you very much!
– Ernesto
Mar 6 at 16:02
1
If you are using Excel 2013 or later, or Excel 365, you can use flash fill. No add-ins necessary at all.
– Vaibhav Garg
Mar 12 at 12:02
add a comment |
I use Excel RegEx Find/Replace add-in, and with that you can use this formula to change format:=RegExReplace(A1,"(+43 d{2})(d) ","$1 $2")
You can see here how the regular expression part works: https://regex101.com/r/ccLkRK/1
answered Mar 6 at 15:45
Máté JuhászMáté Juhász
14.8k63552
I use Excel RegEx Find/Replace add-in, and with that you can use this formula to change format:=RegExReplace(A1,"(+43 d{2})(d) ","$1 $2")
You can see here how the regular expression part works: https://regex101.com/r/ccLkRK/1
answered Mar 6 at 15:45
Máté JuhászMáté Juhász
14.8k63552
answered Mar 6 at 15:45
Máté JuhászMáté Juhász
14.8k63552
answered Mar 6 at 15:45
Máté JuhászMáté Juhász
14.8k63552
answered Mar 6 at 15:45
Máté JuhászMáté Juhász
14.8k63552
14.8k63552
thank you very much!
– Ernesto
Mar 6 at 16:02
1
If you are using Excel 2013 or later, or Excel 365, you can use flash fill. No add-ins necessary at all.
– Vaibhav Garg
Mar 12 at 12:02
add a comment |
thank you very much!
– Ernesto
Mar 6 at 16:02
1
If you are using Excel 2013 or later, or Excel 365, you can use flash fill. No add-ins necessary at all.
– Vaibhav Garg
Mar 12 at 12:02
thank you very much!
– Ernesto
Mar 6 at 16:02
thank you very much!
– Ernesto
Mar 6 at 16:02
1
1
If you are using Excel 2013 or later, or Excel 365, you can use flash fill. No add-ins necessary at all.
– Vaibhav Garg
Mar 12 at 12:02
If you are using Excel 2013 or later, or Excel 365, you can use flash fill. No add-ins necessary at all.
– Vaibhav Garg
Mar 12 at 12:02
add a comment |
I assume that you are using a current version of Excel (2013+ or 365). In an adjacent column, next to the input, just type out a few outputs by hand. Excel should infer the pattern and offer to Flash Fill automatically. Just press Enter, and Done!
If it does not, you can click on Fill on the Home tab, and select Flash Fill.
No Add-ins necessary.
As seen in the screenshot, 2 examples was all it took.
answered Mar 12 at 11:43
Vaibhav GargVaibhav Garg
9312
add a comment |
I assume that you are using a current version of Excel (2013+ or 365). In an adjacent column, next to the input, just type out a few outputs by hand. Excel should infer the pattern and offer to Flash Fill automatically. Just press Enter, and Done!
If it does not, you can click on Fill on the Home tab, and select Flash Fill.
No Add-ins necessary.
As seen in the screenshot, 2 examples was all it took.
answered Mar 12 at 11:43
Vaibhav GargVaibhav Garg
9312
add a comment |
I assume that you are using a current version of Excel (2013+ or 365). In an adjacent column, next to the input, just type out a few outputs by hand. Excel should infer the pattern and offer to Flash Fill automatically. Just press Enter, and Done!
If it does not, you can click on Fill on the Home tab, and select Flash Fill.
No Add-ins necessary.
As seen in the screenshot, 2 examples was all it took.
answered Mar 12 at 11:43
Vaibhav GargVaibhav Garg
9312
I assume that you are using a current version of Excel (2013+ or 365). In an adjacent column, next to the input, just type out a few outputs by hand. Excel should infer the pattern and offer to Flash Fill automatically. Just press Enter, and Done!
If it does not, you can click on Fill on the Home tab, and select Flash Fill.
No Add-ins necessary.
As seen in the screenshot, 2 examples was all it took.
answered Mar 12 at 11:43
Vaibhav GargVaibhav Garg
9312
answered Mar 12 at 11:43
Vaibhav GargVaibhav Garg
9312
answered Mar 12 at 11:43
Vaibhav GargVaibhav Garg
9312
answered Mar 12 at 11:43
Vaibhav GargVaibhav Garg
9312
9312
add a comment |
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%2f1411836%2fformat-telephone-numbers-in-excel%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
1
Easiest thing to do would be to copy the column data out to Notepad++, use some regex to change the pattern, then paste it all back into Excel. As for the actual regex, i can't help with that.
– spikey_richie
Mar 6 at 15:39