How to split a string based on ":" in MS-Excel?

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

user1518659

Posted 2012-10-03T16:16:00.823

Reputation:

Answers

Go to Data tab, then Text to Columns option. Later, choose "Delimited" option and then select "other" and put any delimiter you want.

BrOSs

Posted 2012-10-03T16:16:00.823

Reputation: 825

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))

nutsch

Posted 2012-10-03T16:16:00.823

Reputation: 1 923

This is a brilliant solution – jsg – 2017-06-28T10:09:59.480

2The 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 – 2013-07-16T14:43:55.920

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

Jamie Bull

Posted 2012-10-03T16:16:00.823

Reputation: 503

1This is perfect for splitting a URL into its component parts. – Underverse – 2015-06-02T01:42:31.760

1Very Nice, didnt know it was so easy to create your own formulas – cowls – 2014-05-13T11:13:38.627

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))

Hardi Uutma

Posted 2012-10-03T16:16:00.823

Reputation: 81

1Welcome 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 – 2016-09-05T18:02:37.333

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 – 2016-09-07T08:11:10.033

Excel says that this is not a valid formula when you paste it into a cell. Please check and update. – thilina R – 2016-09-23T14:01:32.963

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 – 2016-09-25T06:39:59.513

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 – 2016-12-15T18:01:55.490

Great! Very elegant solution - unlimited columns, only limitation is the size per String of 998. – Paschi – 2017-01-17T15:32:31.863