Extracting Data

Katich

Board Regular
Joined
Jan 22, 2008
Messages
107
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

<colgroup><col></colgroup><tbody>
</tbody>
So my result for the above would be
2
6
10

Any help would be appreciated.

Thank you,
Frank
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,494
Office Version
365
Platform
Windows
Use HOUR, i.e.
=HOUR(A1)
 

Katich

Board Regular
Joined
Jan 22, 2008
Messages
107
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,494
Office Version
365
Platform
Windows
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

Board Regular
Joined
Jan 22, 2008
Messages
107
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,494
Office Version
365
Platform
Windows
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.
 

Katich

Board Regular
Joined
Jan 22, 2008
Messages
107
Awesome!! I'm on the same page as you now and it worked! Thank you!!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,494
Office Version
365
Platform
Windows
You are welcome!
 

Forum statistics

Threads
1,082,344
Messages
5,364,803
Members
400,814
Latest member
gangstar67

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top