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

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Trances

Board Regular
Joined
Nov 16, 2006
Messages
142
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

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,358
Office Version
  1. 365
Platform
  1. Windows
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

Trances

Board Regular
Joined
Nov 16, 2006
Messages
142
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

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,772
You should be able to convert the dates into actual dates using the formula =DATEVALUE.
 
Upvote 0

Trances

Board Regular
Joined
Nov 16, 2006
Messages
142
thats an idea the dates were giving me major pains
BUT

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

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,772
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

Trances

Board Regular
Joined
Nov 16, 2006
Messages
142
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

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
To get the couse code,

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

HTH
 
Upvote 0

Forum statistics

Threads
1,191,559
Messages
5,987,290
Members
440,089
Latest member
FahadSid

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
Top