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

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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
 
Last edited by a moderator:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
You should be able to convert the dates into actual dates using the formula =DATEVALUE.
 
Upvote 0
thats an idea the dates were giving me major pains
BUT

=DATEVALUE(LEFT(report!C12,12))
Gives an ERROR
#VALUE!
 
Upvote 0
Works for me - do you have the Analysis ToolPak installed?
Book1
ABCD
116Apr,20079:26AMBST
2
316-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
 
Upvote 0
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)))
 
Upvote 0
To get the couse code,

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

HTH
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top