Database Export Fix( Simple but Many LEFT,MID,FIND etc)

[Trances]

Table removed

 

[Trances]

Now below is how I am trying to formatt it
You can see there is
1. 5 digit User Number
=MID(report!A11,(FIND("(",report!A11))+1,5)
2. User Name
=LEFT(report!A11,FIND("(",report!A11)-1)
3. Course Code
=MID(report!B11,(FIND("(",report!B11))+1,16)
4. Start Date
=LEFT(report!C11,12)
5. End Date
=LEFT(report!D11,12)

Of course Its not perfect as for Course Code still has the ) and the Dates cant be sorted.
The idea is report is reported and based on the back sheet and then the Front sheet automatlly converts the data to the correct formatt. Similar to below but with the to mistakes fixed and perhaps better formuals than hte ones I have used

 

[Norie]

I don't think you need any of those functions, though they probably can be used.

I would suggest using Data>Text to columns... and inserting/pasting columns.

 

[Trances]

Thanks Norie but that would not work as the USER would have to do it. What I want is they paste there data on Sheet 2 and it appears on Sheet 1 in the correct format

Also the formuals I have created there are far from perfect and dont give the 100% right result. I cant think of any other way to ensure the user gets data in the correct format.

 

[njimack]

You should be able to convert the dates into actual dates using the formula =DATEVALUE.

 

[Trances]

thats an idea the dates were giving me major pains
BUT

=DATEVALUE(LEFT(report!C12,12))
Gives an ERROR
#VALUE!

 

[njimack]

Works for me - do you have the Analysis ToolPak installed?

Book1
	A	B	C	D
1	16Apr,20079:26AMBST
2
3	16-04-07
Sheet1

EDIT: Might be more trouble than it's worth, but try using MID, FIND, etc. to format the date as I have

 

[Trances]

This gives you the year
=MID(report!C11,FIND(", ",report!C11)+2,4)

This gives you the month
=LEFT(report!C11,3)

This gives you the day
=IF(ISERROR(FIND(",",(MID(report!C11,FIND(" ",report!C11)+1,2)),1)),MID(report!C11,FIND(" ",report!C11)+1,2),LEFT(MID(report!C11,FIND(" ",report!C11)+1,2)))

 

[Trances]

Whihc now works !

 

[Krishnakumar]

To get the couse code,

=MID(A1,FIND("(",A1)+1,SUM(FIND({"(",")"},SUBSTITUTE(A1,"_",")"))*{-1,1})-1)

HTH