How to split a string based on “:” in MS-Excel?How do I add VBA in MS Office?Excel- how to piece out...
How do Chazal know that the descendants of a Mamzer may never marry into the general populace?
Injecting creativity into a cookbook
Lagrangian corresponding to these equations of motion
Why did other German political parties disband so fast when Hitler was appointed chancellor?
Why has the mole been redefined for 2019?
Why publish a research paper when a blog post or a lecture slide can have more citation count than a journal paper?
Can I write a book of my D&D game?
How to say "Brexit" in Latin?
Caruana vs Carlsen game 10 (WCC) why not 18...Nxb6?
In Linux what happens if 1000 files in a directory are moved to another location while another 300 files were added to the source directory?
Are there any modern advantages of a fire piston?
Why is the copy constructor called twice in this code snippet?
Can a hotel cancel a confirmed reservation?
Blindfold battle as a gladiatorial spectacle - what are the tactics and communication methods?
Eww, those bytes are gross
Why do neural networks need so many training examples to perform?
Can we use the stored gravitational potential energy of a building to produce power?
What is the purpose of easy combat scenarios that don't need resource expenditure?
Who is this Ant Woman character in this image alongside the Wasp?
Do authors have to be politically correct in article-writing?
How can I deliver in-universe written lore to players without it being dry exposition?
Dilemma of explaining to interviewer that he is the reason for declining second interview
Normalization for two bulk RNA-Seq samples to enable reliable fold-change estimation between genes
Equation with several exponents
How to split a string based on “:” in MS-Excel?
How do I add VBA in MS Office?Excel- how to piece out parts of the text in a cell into a different cellCombine 3 columns into one in Excel 2011 for MacExcel two columns with word list - find differencesSplit Address in Excel Cellhow to split character & digit in excel?Split Text into Columns Function in ExcelSelect text to display based on matching a symbol in another rowSplit one column into two based on values in columnMicrosoft Excel: How to invert values based on a limit?Excel - Split one columns values into 2 columns, split on every 2nd rowPopulate a column with text (with no blank spaces) from another column based on an adjacent word in third column
My excel column is filled with words like this:
1.) ABC:DCF
2.) DCF:FED
I want to split each word based on " : " and put the result in adjacent columns such that "ABC:DCF" in cell "A:1" becomes "ABC" in cell "B:1" and "DCF" in cell "C:1" and also corresponding values in each column. How to do this?
microsoft-excel
edited Nov 15 '13 at 14:15
Andrea
1,43631316
migrated from stackoverflow.com Oct 4 '12 at 14:18
This question came from our site for professional and enthusiast programmers.
add a comment |
My excel column is filled with words like this:
1.) ABC:DCF
2.) DCF:FED
I want to split each word based on " : " and put the result in adjacent columns such that "ABC:DCF" in cell "A:1" becomes "ABC" in cell "B:1" and "DCF" in cell "C:1" and also corresponding values in each column. How to do this?
microsoft-excel
edited Nov 15 '13 at 14:15
Andrea
1,43631316
migrated from stackoverflow.com Oct 4 '12 at 14:18
This question came from our site for professional and enthusiast programmers.
add a comment |
My excel column is filled with words like this:
1.) ABC:DCF
2.) DCF:FED
I want to split each word based on " : " and put the result in adjacent columns such that "ABC:DCF" in cell "A:1" becomes "ABC" in cell "B:1" and "DCF" in cell "C:1" and also corresponding values in each column. How to do this?
microsoft-excel
edited Nov 15 '13 at 14:15
Andrea
1,43631316
My excel column is filled with words like this:
1.) ABC:DCF
2.) DCF:FED
I want to split each word based on " : " and put the result in adjacent columns such that "ABC:DCF" in cell "A:1" becomes "ABC" in cell "B:1" and "DCF" in cell "C:1" and also corresponding values in each column. How to do this?
microsoft-excel
microsoft-excel
edited Nov 15 '13 at 14:15
Andrea
1,43631316
edited Nov 15 '13 at 14:15
Andrea
1,43631316
edited Nov 15 '13 at 14:15
Andrea
1,43631316
edited Nov 15 '13 at 14:15
Andrea
1,43631316
edited Nov 15 '13 at 14:15
Andrea
1,43631316
1,43631316
asked Oct 3 '12 at 16:16
user1518659
migrated from stackoverflow.com Oct 4 '12 at 14:18
This question came from our site for professional and enthusiast programmers.
migrated from stackoverflow.com Oct 4 '12 at 14:18
This question came from our site for professional and enthusiast programmers.
add a comment |
add a comment |
5 Answers
5
active
oldest
votes
Go to Data tab, then Text to Columns option. Later, choose "Delimited" option and then select "other" and put any delimiter you want.
edited Oct 4 '12 at 14:52
Journeyman Geek♦
112k44217371
answered Oct 3 '12 at 16:20
BrOSsBrOSs
81575
add a comment |
Text to columns will work. Another option, if you want to keep the original value, is to use formulas:
in B1
=left(a1,find(":",a1)-1)
in C1
=mid(a1,find(":",a1)+1,len(a1))
answered Oct 3 '12 at 16:34
nutschnutsch
1,843914
2
The original value can be kept even with the other solution (you can specify a different column to store the new values), but I like this solution better because it allows to always have up-to-date values (i.e. if you modify A1, B1 and C1 will update, while the text-to-column option does not).
– psychowood
Jul 16 '13 at 14:43
This is a brilliant solution
– jsg
Jun 28 '17 at 10:09
add a comment |
If you can use VBA then you can make use of the Split() function. Here's a User-Defined Function (UDF) that you can use in a cell. It splits on your choice of character and returns the nth element of the split list.
See How do I add VBA in MS Office?
for information on how to define a UDF.
Function STR_SPLIT(str, sep, n) As String
Dim V() As String
V = Split(str, sep)
STR_SPLIT = V(n - 1)
End Function
So you'd need to enter:
=STR_SPLIT(A1, ":", 1) // for the first half
=STR_SPLIT(A1, ":", 2) // for the second half
edited Mar 20 '17 at 10:17
Community♦
1
answered Oct 3 '12 at 18:42
Jamie BullJamie Bull
4621516
1
Very Nice, didnt know it was so easy to create your own formulas
– cowls
May 13 '14 at 11:13
1
This is perfect for splitting a URL into its component parts.
– Underverse
Jun 2 '15 at 1:42
add a comment |
Paste it to B1 and fill it to columns on right and rows down:
=TRIM(MID(SUBSTITUTE($A1,":",REPT(" ",999)),COLUMNS($A:A)*999-998,999))
Edit: I previously posted localized version of the formula, where ',' was replaced with ';'. That doesn't work in US-version of Excel:
=TRIM(MID(SUBSTITUTE($A1;":";REPT(" ";999));COLUMNS($A:A)*999-998;999))
answered Sep 5 '16 at 16:44
Hardi UutmaHardi Uutma
6112
1
Welcome to Super User. Could you add a few sentences to your answer to explain what this does and how it works? That will enhance its educational value. Thanks.
– fixer1234
Sep 5 '16 at 18:02
Yea, sure. It does the same thing what Text to Columns from Data tab does, except it does't it with formula. You could replace the ":" by a different Delimiter or refer to a delimiter from the other cell.
– Hardi Uutma
Sep 7 '16 at 8:11
Excel says that this is not a valid formula when you paste it into a cell. Please check and update.
– thilina R
Sep 23 '16 at 14:01
Hi thilina R! Thank you for notifying. I made the adjustment for the US-version of Excel. Please let me know if you have any trouble with that now or if anything is unclear.
– Hardi Uutma
Sep 25 '16 at 6:39
Very nice. The only answer so far that allows you to deal with as many delimiters as you may want, without creating your own function.
– CWilson
Dec 15 '16 at 18:01
|
show 1 more comment
Sorry to bump an old thread, but I found it useful and wanted to really understand what it was doing. I looked at the formula for a while before figuring out how it works...
Here's what it does.
The SUBSTITUTE($A1;":";REPT(" ";999)) substitutes your delimiters (the character : in this case) into 999 spaces.
The COLUMNS($A:A)*999-998 part works out how many columns the formula has been moved to the right of the formula's original place then multiplies that by 999 and deducts 998 from that number. This part is used to make sure that the "real" data you want is selected out of the original cell by the "MID" function and has some spaces around it on both sided. It assumes that the length of the original string including the delimiters is less than 999 characters long.
The MID(SUBSTITUTE($A1;":";REPT(" ";999));COLUMNS($A:A)*999-998;999) function should return 999 characters from your string which will include a whole heap of spaces (where delimiters used to be) and the "real" data for that column in between.
The Trim function just gets rid of all those spaces.
I would never have thought of solving the problem that way. Well done Hardi.
answered 20 mins ago
PeterPeter
1
New contributor
Peter is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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%2f483419%2fhow-to-split-a-string-based-on-in-ms-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
5 Answers
5
active
oldest
votes
5 Answers
5
active
oldest
votes
active
oldest
votes
active
oldest
votes
Go to Data tab, then Text to Columns option. Later, choose "Delimited" option and then select "other" and put any delimiter you want.
edited Oct 4 '12 at 14:52
Journeyman Geek♦
112k44217371
answered Oct 3 '12 at 16:20
BrOSsBrOSs
81575
add a comment |
Go to Data tab, then Text to Columns option. Later, choose "Delimited" option and then select "other" and put any delimiter you want.
edited Oct 4 '12 at 14:52
Journeyman Geek♦
112k44217371
answered Oct 3 '12 at 16:20
BrOSsBrOSs
81575
add a comment |
Go to Data tab, then Text to Columns option. Later, choose "Delimited" option and then select "other" and put any delimiter you want.
edited Oct 4 '12 at 14:52
Journeyman Geek♦
112k44217371
answered Oct 3 '12 at 16:20
BrOSsBrOSs
81575
Go to Data tab, then Text to Columns option. Later, choose "Delimited" option and then select "other" and put any delimiter you want.
edited Oct 4 '12 at 14:52
Journeyman Geek♦
112k44217371
answered Oct 3 '12 at 16:20
BrOSsBrOSs
81575
edited Oct 4 '12 at 14:52
Journeyman Geek♦
112k44217371
edited Oct 4 '12 at 14:52
Journeyman Geek♦
112k44217371
edited Oct 4 '12 at 14:52
Journeyman Geek♦
112k44217371
112k44217371
answered Oct 3 '12 at 16:20
BrOSsBrOSs
81575
answered Oct 3 '12 at 16:20
BrOSsBrOSs
81575
answered Oct 3 '12 at 16:20
BrOSsBrOSs
81575
81575
add a comment |
add a comment |
Text to columns will work. Another option, if you want to keep the original value, is to use formulas:
in B1
=left(a1,find(":",a1)-1)
in C1
=mid(a1,find(":",a1)+1,len(a1))
answered Oct 3 '12 at 16:34
nutschnutsch
1,843914
2
The original value can be kept even with the other solution (you can specify a different column to store the new values), but I like this solution better because it allows to always have up-to-date values (i.e. if you modify A1, B1 and C1 will update, while the text-to-column option does not).
– psychowood
Jul 16 '13 at 14:43
This is a brilliant solution
– jsg
Jun 28 '17 at 10:09
add a comment |
Text to columns will work. Another option, if you want to keep the original value, is to use formulas:
in B1
=left(a1,find(":",a1)-1)
in C1
=mid(a1,find(":",a1)+1,len(a1))
answered Oct 3 '12 at 16:34
nutschnutsch
1,843914
2
The original value can be kept even with the other solution (you can specify a different column to store the new values), but I like this solution better because it allows to always have up-to-date values (i.e. if you modify A1, B1 and C1 will update, while the text-to-column option does not).
– psychowood
Jul 16 '13 at 14:43
This is a brilliant solution
– jsg
Jun 28 '17 at 10:09
add a comment |
Text to columns will work. Another option, if you want to keep the original value, is to use formulas:
in B1
=left(a1,find(":",a1)-1)
in C1
=mid(a1,find(":",a1)+1,len(a1))
answered Oct 3 '12 at 16:34
nutschnutsch
1,843914
Text to columns will work. Another option, if you want to keep the original value, is to use formulas:
in B1
=left(a1,find(":",a1)-1)
in C1
=mid(a1,find(":",a1)+1,len(a1))
answered Oct 3 '12 at 16:34
nutschnutsch
1,843914
answered Oct 3 '12 at 16:34
nutschnutsch
1,843914
answered Oct 3 '12 at 16:34
nutschnutsch
1,843914
answered Oct 3 '12 at 16:34
nutschnutsch
1,843914
1,843914
2
The original value can be kept even with the other solution (you can specify a different column to store the new values), but I like this solution better because it allows to always have up-to-date values (i.e. if you modify A1, B1 and C1 will update, while the text-to-column option does not).
– psychowood
Jul 16 '13 at 14:43
This is a brilliant solution
– jsg
Jun 28 '17 at 10:09
add a comment |
2
The original value can be kept even with the other solution (you can specify a different column to store the new values), but I like this solution better because it allows to always have up-to-date values (i.e. if you modify A1, B1 and C1 will update, while the text-to-column option does not).
– psychowood
Jul 16 '13 at 14:43
This is a brilliant solution
– jsg
Jun 28 '17 at 10:09
2
2
The original value can be kept even with the other solution (you can specify a different column to store the new values), but I like this solution better because it allows to always have up-to-date values (i.e. if you modify A1, B1 and C1 will update, while the text-to-column option does not).
– psychowood
Jul 16 '13 at 14:43
The original value can be kept even with the other solution (you can specify a different column to store the new values), but I like this solution better because it allows to always have up-to-date values (i.e. if you modify A1, B1 and C1 will update, while the text-to-column option does not).
– psychowood
Jul 16 '13 at 14:43
This is a brilliant solution
– jsg
Jun 28 '17 at 10:09
This is a brilliant solution
– jsg
Jun 28 '17 at 10:09
add a comment |
If you can use VBA then you can make use of the Split() function. Here's a User-Defined Function (UDF) that you can use in a cell. It splits on your choice of character and returns the nth element of the split list.
See How do I add VBA in MS Office?
for information on how to define a UDF.
Function STR_SPLIT(str, sep, n) As String
Dim V() As String
V = Split(str, sep)
STR_SPLIT = V(n - 1)
End Function
So you'd need to enter:
=STR_SPLIT(A1, ":", 1) // for the first half
=STR_SPLIT(A1, ":", 2) // for the second half
edited Mar 20 '17 at 10:17
Community♦
1
answered Oct 3 '12 at 18:42
Jamie BullJamie Bull
4621516
1
Very Nice, didnt know it was so easy to create your own formulas
– cowls
May 13 '14 at 11:13
1
This is perfect for splitting a URL into its component parts.
– Underverse
Jun 2 '15 at 1:42
add a comment |
If you can use VBA then you can make use of the Split() function. Here's a User-Defined Function (UDF) that you can use in a cell. It splits on your choice of character and returns the nth element of the split list.
See How do I add VBA in MS Office?
for information on how to define a UDF.
Function STR_SPLIT(str, sep, n) As String
Dim V() As String
V = Split(str, sep)
STR_SPLIT = V(n - 1)
End Function
So you'd need to enter:
=STR_SPLIT(A1, ":", 1) // for the first half
=STR_SPLIT(A1, ":", 2) // for the second half
edited Mar 20 '17 at 10:17
Community♦
1
answered Oct 3 '12 at 18:42
Jamie BullJamie Bull
4621516
1
Very Nice, didnt know it was so easy to create your own formulas
– cowls
May 13 '14 at 11:13
1
This is perfect for splitting a URL into its component parts.
– Underverse
Jun 2 '15 at 1:42
add a comment |
If you can use VBA then you can make use of the Split() function. Here's a User-Defined Function (UDF) that you can use in a cell. It splits on your choice of character and returns the nth element of the split list.
See How do I add VBA in MS Office?
for information on how to define a UDF.
Function STR_SPLIT(str, sep, n) As String
Dim V() As String
V = Split(str, sep)
STR_SPLIT = V(n - 1)
End Function
So you'd need to enter:
=STR_SPLIT(A1, ":", 1) // for the first half
=STR_SPLIT(A1, ":", 2) // for the second half
edited Mar 20 '17 at 10:17
Community♦
1
answered Oct 3 '12 at 18:42
Jamie BullJamie Bull
4621516
If you can use VBA then you can make use of the Split() function. Here's a User-Defined Function (UDF) that you can use in a cell. It splits on your choice of character and returns the nth element of the split list.
See How do I add VBA in MS Office?
for information on how to define a UDF.
Function STR_SPLIT(str, sep, n) As String
Dim V() As String
V = Split(str, sep)
STR_SPLIT = V(n - 1)
End Function
So you'd need to enter:
=STR_SPLIT(A1, ":", 1) // for the first half
=STR_SPLIT(A1, ":", 2) // for the second half
edited Mar 20 '17 at 10:17
Community♦
1
answered Oct 3 '12 at 18:42
Jamie BullJamie Bull
4621516
edited Mar 20 '17 at 10:17
Community♦
1
edited Mar 20 '17 at 10:17
Community♦
1
edited Mar 20 '17 at 10:17
Community♦
1
1
answered Oct 3 '12 at 18:42
Jamie BullJamie Bull
4621516
answered Oct 3 '12 at 18:42
Jamie BullJamie Bull
4621516
answered Oct 3 '12 at 18:42
Jamie BullJamie Bull
4621516
4621516
1
Very Nice, didnt know it was so easy to create your own formulas
– cowls
May 13 '14 at 11:13
1
This is perfect for splitting a URL into its component parts.
– Underverse
Jun 2 '15 at 1:42
add a comment |
1
Very Nice, didnt know it was so easy to create your own formulas
– cowls
May 13 '14 at 11:13
1
This is perfect for splitting a URL into its component parts.
– Underverse
Jun 2 '15 at 1:42
1
1
Very Nice, didnt know it was so easy to create your own formulas
– cowls
May 13 '14 at 11:13
Very Nice, didnt know it was so easy to create your own formulas
– cowls
May 13 '14 at 11:13
1
1
This is perfect for splitting a URL into its component parts.
– Underverse
Jun 2 '15 at 1:42
This is perfect for splitting a URL into its component parts.
– Underverse
Jun 2 '15 at 1:42
add a comment |
Paste it to B1 and fill it to columns on right and rows down:
=TRIM(MID(SUBSTITUTE($A1,":",REPT(" ",999)),COLUMNS($A:A)*999-998,999))
Edit: I previously posted localized version of the formula, where ',' was replaced with ';'. That doesn't work in US-version of Excel:
=TRIM(MID(SUBSTITUTE($A1;":";REPT(" ";999));COLUMNS($A:A)*999-998;999))
answered Sep 5 '16 at 16:44
Hardi UutmaHardi Uutma
6112
1
Welcome to Super User. Could you add a few sentences to your answer to explain what this does and how it works? That will enhance its educational value. Thanks.
– fixer1234
Sep 5 '16 at 18:02
Yea, sure. It does the same thing what Text to Columns from Data tab does, except it does't it with formula. You could replace the ":" by a different Delimiter or refer to a delimiter from the other cell.
– Hardi Uutma
Sep 7 '16 at 8:11
Excel says that this is not a valid formula when you paste it into a cell. Please check and update.
– thilina R
Sep 23 '16 at 14:01
Hi thilina R! Thank you for notifying. I made the adjustment for the US-version of Excel. Please let me know if you have any trouble with that now or if anything is unclear.
– Hardi Uutma
Sep 25 '16 at 6:39
Very nice. The only answer so far that allows you to deal with as many delimiters as you may want, without creating your own function.
– CWilson
Dec 15 '16 at 18:01
|
show 1 more comment
Paste it to B1 and fill it to columns on right and rows down:
=TRIM(MID(SUBSTITUTE($A1,":",REPT(" ",999)),COLUMNS($A:A)*999-998,999))
Edit: I previously posted localized version of the formula, where ',' was replaced with ';'. That doesn't work in US-version of Excel:
=TRIM(MID(SUBSTITUTE($A1;":";REPT(" ";999));COLUMNS($A:A)*999-998;999))
answered Sep 5 '16 at 16:44
Hardi UutmaHardi Uutma
6112
1
Welcome to Super User. Could you add a few sentences to your answer to explain what this does and how it works? That will enhance its educational value. Thanks.
– fixer1234
Sep 5 '16 at 18:02
Yea, sure. It does the same thing what Text to Columns from Data tab does, except it does't it with formula. You could replace the ":" by a different Delimiter or refer to a delimiter from the other cell.
– Hardi Uutma
Sep 7 '16 at 8:11
Excel says that this is not a valid formula when you paste it into a cell. Please check and update.
– thilina R
Sep 23 '16 at 14:01
Hi thilina R! Thank you for notifying. I made the adjustment for the US-version of Excel. Please let me know if you have any trouble with that now or if anything is unclear.
– Hardi Uutma
Sep 25 '16 at 6:39
Very nice. The only answer so far that allows you to deal with as many delimiters as you may want, without creating your own function.
– CWilson
Dec 15 '16 at 18:01
|
show 1 more comment
Paste it to B1 and fill it to columns on right and rows down:
=TRIM(MID(SUBSTITUTE($A1,":",REPT(" ",999)),COLUMNS($A:A)*999-998,999))
Edit: I previously posted localized version of the formula, where ',' was replaced with ';'. That doesn't work in US-version of Excel:
=TRIM(MID(SUBSTITUTE($A1;":";REPT(" ";999));COLUMNS($A:A)*999-998;999))
answered Sep 5 '16 at 16:44
Hardi UutmaHardi Uutma
6112
Paste it to B1 and fill it to columns on right and rows down:
=TRIM(MID(SUBSTITUTE($A1,":",REPT(" ",999)),COLUMNS($A:A)*999-998,999))
Edit: I previously posted localized version of the formula, where ',' was replaced with ';'. That doesn't work in US-version of Excel:
=TRIM(MID(SUBSTITUTE($A1;":";REPT(" ";999));COLUMNS($A:A)*999-998;999))
answered Sep 5 '16 at 16:44
Hardi UutmaHardi Uutma
6112
edited Sep 25 '16 at 6:35
answered Sep 5 '16 at 16:44
Hardi UutmaHardi Uutma
6112
answered Sep 5 '16 at 16:44
Hardi UutmaHardi Uutma
6112
answered Sep 5 '16 at 16:44
Hardi UutmaHardi Uutma
6112
6112
1
Welcome to Super User. Could you add a few sentences to your answer to explain what this does and how it works? That will enhance its educational value. Thanks.
– fixer1234
Sep 5 '16 at 18:02
Yea, sure. It does the same thing what Text to Columns from Data tab does, except it does't it with formula. You could replace the ":" by a different Delimiter or refer to a delimiter from the other cell.
– Hardi Uutma
Sep 7 '16 at 8:11
Excel says that this is not a valid formula when you paste it into a cell. Please check and update.
– thilina R
Sep 23 '16 at 14:01
Hi thilina R! Thank you for notifying. I made the adjustment for the US-version of Excel. Please let me know if you have any trouble with that now or if anything is unclear.
– Hardi Uutma
Sep 25 '16 at 6:39
Very nice. The only answer so far that allows you to deal with as many delimiters as you may want, without creating your own function.
– CWilson
Dec 15 '16 at 18:01
|
show 1 more comment
1
Welcome to Super User. Could you add a few sentences to your answer to explain what this does and how it works? That will enhance its educational value. Thanks.
– fixer1234
Sep 5 '16 at 18:02
Yea, sure. It does the same thing what Text to Columns from Data tab does, except it does't it with formula. You could replace the ":" by a different Delimiter or refer to a delimiter from the other cell.
– Hardi Uutma
Sep 7 '16 at 8:11
Excel says that this is not a valid formula when you paste it into a cell. Please check and update.
– thilina R
Sep 23 '16 at 14:01
Hi thilina R! Thank you for notifying. I made the adjustment for the US-version of Excel. Please let me know if you have any trouble with that now or if anything is unclear.
– Hardi Uutma
Sep 25 '16 at 6:39
Very nice. The only answer so far that allows you to deal with as many delimiters as you may want, without creating your own function.
– CWilson
Dec 15 '16 at 18:01
1
1
Welcome to Super User. Could you add a few sentences to your answer to explain what this does and how it works? That will enhance its educational value. Thanks.
– fixer1234
Sep 5 '16 at 18:02
Welcome to Super User. Could you add a few sentences to your answer to explain what this does and how it works? That will enhance its educational value. Thanks.
– fixer1234
Sep 5 '16 at 18:02
Yea, sure. It does the same thing what Text to Columns from Data tab does, except it does't it with formula. You could replace the ":" by a different Delimiter or refer to a delimiter from the other cell.
– Hardi Uutma
Sep 7 '16 at 8:11
Yea, sure. It does the same thing what Text to Columns from Data tab does, except it does't it with formula. You could replace the ":" by a different Delimiter or refer to a delimiter from the other cell.
– Hardi Uutma
Sep 7 '16 at 8:11
Excel says that this is not a valid formula when you paste it into a cell. Please check and update.
– thilina R
Sep 23 '16 at 14:01
Excel says that this is not a valid formula when you paste it into a cell. Please check and update.
– thilina R
Sep 23 '16 at 14:01
Hi thilina R! Thank you for notifying. I made the adjustment for the US-version of Excel. Please let me know if you have any trouble with that now or if anything is unclear.
– Hardi Uutma
Sep 25 '16 at 6:39
Hi thilina R! Thank you for notifying. I made the adjustment for the US-version of Excel. Please let me know if you have any trouble with that now or if anything is unclear.
– Hardi Uutma
Sep 25 '16 at 6:39
Very nice. The only answer so far that allows you to deal with as many delimiters as you may want, without creating your own function.
– CWilson
Dec 15 '16 at 18:01
Very nice. The only answer so far that allows you to deal with as many delimiters as you may want, without creating your own function.
– CWilson
Dec 15 '16 at 18:01
|
show 1 more comment
Sorry to bump an old thread, but I found it useful and wanted to really understand what it was doing. I looked at the formula for a while before figuring out how it works...
Here's what it does.
The SUBSTITUTE($A1;":";REPT(" ";999)) substitutes your delimiters (the character : in this case) into 999 spaces.
The COLUMNS($A:A)*999-998 part works out how many columns the formula has been moved to the right of the formula's original place then multiplies that by 999 and deducts 998 from that number. This part is used to make sure that the "real" data you want is selected out of the original cell by the "MID" function and has some spaces around it on both sided. It assumes that the length of the original string including the delimiters is less than 999 characters long.
The MID(SUBSTITUTE($A1;":";REPT(" ";999));COLUMNS($A:A)*999-998;999) function should return 999 characters from your string which will include a whole heap of spaces (where delimiters used to be) and the "real" data for that column in between.
The Trim function just gets rid of all those spaces.
I would never have thought of solving the problem that way. Well done Hardi.
answered 20 mins ago
PeterPeter
1
New contributor
Peter is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Sorry to bump an old thread, but I found it useful and wanted to really understand what it was doing. I looked at the formula for a while before figuring out how it works...
Here's what it does.
The SUBSTITUTE($A1;":";REPT(" ";999)) substitutes your delimiters (the character : in this case) into 999 spaces.
The COLUMNS($A:A)*999-998 part works out how many columns the formula has been moved to the right of the formula's original place then multiplies that by 999 and deducts 998 from that number. This part is used to make sure that the "real" data you want is selected out of the original cell by the "MID" function and has some spaces around it on both sided. It assumes that the length of the original string including the delimiters is less than 999 characters long.
The MID(SUBSTITUTE($A1;":";REPT(" ";999));COLUMNS($A:A)*999-998;999) function should return 999 characters from your string which will include a whole heap of spaces (where delimiters used to be) and the "real" data for that column in between.
The Trim function just gets rid of all those spaces.
I would never have thought of solving the problem that way. Well done Hardi.
answered 20 mins ago
PeterPeter
1
New contributor
Peter is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Sorry to bump an old thread, but I found it useful and wanted to really understand what it was doing. I looked at the formula for a while before figuring out how it works...
Here's what it does.
The SUBSTITUTE($A1;":";REPT(" ";999)) substitutes your delimiters (the character : in this case) into 999 spaces.
The COLUMNS($A:A)*999-998 part works out how many columns the formula has been moved to the right of the formula's original place then multiplies that by 999 and deducts 998 from that number. This part is used to make sure that the "real" data you want is selected out of the original cell by the "MID" function and has some spaces around it on both sided. It assumes that the length of the original string including the delimiters is less than 999 characters long.
The MID(SUBSTITUTE($A1;":";REPT(" ";999));COLUMNS($A:A)*999-998;999) function should return 999 characters from your string which will include a whole heap of spaces (where delimiters used to be) and the "real" data for that column in between.
The Trim function just gets rid of all those spaces.
I would never have thought of solving the problem that way. Well done Hardi.
answered 20 mins ago
PeterPeter
1
New contributor
Peter is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Sorry to bump an old thread, but I found it useful and wanted to really understand what it was doing. I looked at the formula for a while before figuring out how it works...
Here's what it does.
The SUBSTITUTE($A1;":";REPT(" ";999)) substitutes your delimiters (the character : in this case) into 999 spaces.
The COLUMNS($A:A)*999-998 part works out how many columns the formula has been moved to the right of the formula's original place then multiplies that by 999 and deducts 998 from that number. This part is used to make sure that the "real" data you want is selected out of the original cell by the "MID" function and has some spaces around it on both sided. It assumes that the length of the original string including the delimiters is less than 999 characters long.
The MID(SUBSTITUTE($A1;":";REPT(" ";999));COLUMNS($A:A)*999-998;999) function should return 999 characters from your string which will include a whole heap of spaces (where delimiters used to be) and the "real" data for that column in between.
The Trim function just gets rid of all those spaces.
I would never have thought of solving the problem that way. Well done Hardi.
answered 20 mins ago
PeterPeter
1
New contributor
Peter is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 20 mins ago
PeterPeter
1
New contributor
Peter is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 20 mins ago
PeterPeter
1
answered 20 mins ago
PeterPeter
1
1
New contributor
Peter is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Peter is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Peter is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
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%2f483419%2fhow-to-split-a-string-based-on-in-ms-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
