Trim Data with 5 Sections

[tsroque]

I have the following data in A2:

DATA1:DATA2:DATA3:DATA4:DATA5

I need to extract it as follows

C2 = DATA1 =LEFT(A2,IF(ISERROR(FIND(":",A2,1)),LEN(A2),FIND(":",A2,1)-1))
D2 = DATA2
E2 = DATA3
F2 = DATA4
G2 = DATA5

What would be my formulas in each cell? I can't seem to get passed the first one. Also, some cells may only have since the list builds upon itself (not my list):

DATA1 or
DATA1:DATA2 or
DATA1:DATA2:DATA3
DATA1:DATA2:DATA3:DATA4

Thank you! As always, look forward to what someone comes up!

 

[Smitty]

I'd use Data-->Text to Columns-->Delimited-->Add a colon as the delimiter.

That'll be a lot easier than trying a formula, especially if you'll have variations, which TTC won't care about.

HTH,

 

[tsroque]

Darn it! I always forget about that! Perfect! Thank you!