Extracting Data

[Katich]

Hello,

I'm trying to find a formula to use to pull out anything before the first semi colon.

2:01:00 AM
6:01:00 AM
10:01:00 AM[TABLE="width: 68"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
So my result for the above would be
2
6
10

Any help would be appreciated.

Thank you,
Frank

 

[Joe4]

Use HOUR, i.e.
=HOUR(A1)

 

[Katich]

I tried that but my end result is 0:00 The thing about the data, it's extracted from a database and is entered as a ratio so it should be 2:01 but when it is downloaded, it comes across as a time.

 

[Joe4]

I tried that but my end result is 0:00

Change the format of the cell with the formula to General or Number.

The thing about the data, it's extracted from a database and is entered as a ratio so it should be 2:01 but when it is downloaded, it comes across as a time.

What format does this data come out in? Is it in a Text file?
How do you import this file into Excel?

 

[Katich]

When i change the format for the first one which is "2:01:00 AM", it becomes 0.084028. What i need is the 2. The data comes out in a .csv format from an SQL query. I'm sure i could create something within the query to go ahead and extract the 2 but i'm not very advanced in writing SQL. Maybe that's what i should do rather than create something new in excel?

 

[Joe4]

OK, I think you may be a little confused on what I am telling you.
Whatever cell your entry currently exists in (let's say 2:01:00 AM is in cell A1), you would put the formula in a different cell.
So, maybe in B1, we enter the formula:
=HOUR(A1)
and you change the format of the column with the formula (B), not the column with the original data (A).

The data comes out in a .csv format

I suspected as much. The problem is that if you open CSV files in Excel directly, Excel does its own automatic conversions on the data and "guesses" what the format of each column is. You don't want that. You want to control the format. So you need to open the CSV file in Excel a little bit differently in order to do that.

1. Open a new blank Excel file
2. Go to the Data menu
3. From the Get External Data ribbon, select "From Text"
4. Browse to your CSV file and select it
This will invoke the Text Import Wizard
5. On Step 1, select "Delimited" and click Next
6. On Step 2, select "Comma" and click Next
7. On Step 3, cycle through the columns in the Data Preview pane and select the column with this ratio data in it, and select the Text option from the "Column data format" options
8. Click Finish

This should bring in that column as Text instead of making it Date/Time.

 

[sheetspread]

Try datepart in SQL

 

[Katich]

Awesome!! I'm on the same page as you now and it worked! Thank you!!

 

[Joe4]

You are welcome!