Jump to content

Parsing Data


This topic is 5770 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Happy Saturday everyone!

Unfortunately, my "raw" data looks like this:

9/1/2005 6:05:11

In one cell in an Excel spreadsheet.

Can I parse this data on import so I can get date and time stamp in seperate fields?

I have to admit, I don't know how to parse, and am trying to learn.

Any help?

Steve

Link to comment
Share on other sites

If Lee's answer doesn't help, try this:

In Excel, copy the column and paste it into Word.

In Word, convert the resulting table to text.

Replace " " with "^t". This turns the space to a tab.

Copy the result. Return to Excel. Insert a new column or two (depending on whether you want to keep the original data or not), and paste the tabbed info in. Excel will columnize your info (be wary of how Excel formats the data...)

HTH,

David

Link to comment
Share on other sites

If you want to do this all in FileMaker make 2 additional fields either calculation or autoenter by calculation. A date field and a time field. Assuming that the data is always formatted as you show there is space between the date and the time. We will use this space to separate the two.

We will use the GetAsDate, Left & Position functions to get the date.

Date = GetAsDate(Left(Data, Position(Data; " "; 1; 1) - 1))

We will use the GetAsTime, Right, Lenght & Position functions to get the time.

Time = GetAsTime(Right(Data; Length (Data) - Position(Data; " "; 1; 1)))

Data is the raw data from the import.

Link to comment
Share on other sites

This topic is 5770 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.