Break apart field (2k)

[armitageshanks]

Hi,

Is there a way that I can split some exported data into two fields?
The data per cell is in the format of:

Simon Williams (14/5/1986)
Alan Wilson (18/2/1965)

The Dates are always in brackets.

I'd like to display the text in one column, and the date in another (without the brackets)

[HansV]

Say that the data are in column A, starting in A2.
In B2, enter the formula
<code>
=TRIM(LEFT(A2,FIND("(",A2)-1))
</code>
In C2, enter the formula
<code>
=DATEVALUE(MID(A2,FIND("(",A2)+1,LEN(A2)-FIND("(",A2)-1))
</code>
You'll probably see a number such as 31456 in C2; if so, format C2 as a date.
Select B2:C2, and fill down as far as needed.

Note: you can, of course, use other columns instead.

[armitageshanks]

Thanks, that works a treat!
My only other issue is some of the dates are displayed as (10/4/2006-15/4/2006). Currently VALUE# is displayed as an error.

[HansV]

Change the second formula to
<code>
=MID(A2,FIND("(",A2)+1,LEN(A2)-FIND("(",A2)-1)
</code>
The value will now be a text value, not a real date.

[armitageshanks]

As normal, thank you very much!