Excel 2007 only - here is a combination of worksheet functions, array-
entered in as many columns as you have colons, that will do this
without VBA. Data is in A1.

In B1, formula is:

B1 =LEN(A1)-LEN(SUBSTITUTE(A1,":",""))

In C1:J1, formula is array-entered as follows:

=TRANSPOSE(MID(A1,IFERROR(FIND("^",SUBSTITUTE(A1," :","^",ROW(INDIRECT("1:"&B1+1))-1))
+1,1),IFERROR(FIND("^",SUBSTITUTE(A1,":","^",ROW(I NDIRECT("1:"&B1+1)))),
1+LEN(A1))-
IFERROR(FIND("^",SUBSTITUTE(A1,":","^",ROW(INDIREC T("1:"&B1+1))-1))
+1,1)))

However, it will return #N/A for any cells that will not have values.
In other words, in this example:

A1 ="SALES:EURO:NEO:BUDRO:LALA"

Values in C1:J1 as as follows:

SALES EURO NEO BUDRO LALA #N/A #N/A #N/A

I'm guessing the VBA approach is more efficient in this case.


On Jan 8, 5:08 pm, Jennifer Cali
wrote:
I want to find out the what the formula would be to perform a function
similar to what the "Text to Column" currently does.

I have a set of data:
CCS:OPS:S/M
GA:HR:G/A
SALES:EURO:NEO

I want to write TWO functions that would "cut" the data at the colon.

FIRST function would return just the first part of the dataset, resulting in:
CCS
GA
SALES

SECOND function would return the first and second parts of the dataset,
resulting in:
CCS:OPS
GA:HR
SALES:EURO

Any thoughts?
--
Thank you! - Jennifer