Importing text data to numbers!?

**Travis.gunn** · 02-20-2016, 04:29 PM

Hey guys,

I am not very efficient at excel and I have hit a brick wall. I am importing data as a .xls and having it be automatically calculated.
My imported data is a combination of numbers and text (7:30PM). It is copied over to a second sheet on the file and I need it to show that time and in a second cell, subtract 30 minutes. I can do the formula pretty easy if the numbers are in decimal format. Is there a way I can have all the imported information in a cell be automatically changed to decimal?

Or, does anyone know how to subtract 30 minutes from a cell that reads (7:30PM)

Thanks!

**BadlySpelledBuoy** · 02-20-2016, 04:50 PM

Assuming your data is in cell A1, the below formula will subtract 30 minutes from it:

Formula:

Please Login or Register  to view this content.

BSB

**newdoverman** · 02-20-2016, 05:45 PM

It is difficult to tell what the text is. From your description it could be 7:30PM or (7:30PM).
If the value is 7:30PM this will extract the time into Excel recognizable time. Format as h:mm AM/PM

Formula:

Please Login or Register  to view this content.

If the text is (7:30PM) then this will extract the time as an Excel recognizable time. Format as h:mm as AM/PM

Formula:

Please Login or Register  to view this content.

To have the formula on sheet2 with the copied data on sheet1 this will do it

Formula:

Please Login or Register  to view this content.

Use this to subtract 30 minutes from either of the times calculated above

Formula:

Please Login or Register  to view this content.

**Travis.gunn** · 02-20-2016, 06:35 PM

I am a little confused about where I enter in the formula to convert this? can you take a look at my spread sheet?

The sheet master is where I copy my extracted data from.
The sheet allocation A1:A40, B1:40 is where I need to convert the time

**newdoverman** · 02-20-2016, 07:16 PM

I would take a look but you haven't uploaded a workbook.
Seeing that you say that you want the converted time to be in B1:B40 enter this in B1 and fill down:

Formula:

Please Login or Register  to view this content.

OR if you have parentheses around the time (7:30PM) enter this in B1 and fill down

Formula:

Please Login or Register  to view this content.

Then in C1 enter this and fill down to subtract 30 minutes

Formula:

Please Login or Register  to view this content.

**Travis.gunn** · 02-21-2016, 06:11 PM

I don't know how to upload my file. but here are two screen shots of what I'm doing.

master page.JPG

this is what my imported data looks like. I pull this from a program that my job uses and its in PDF format. I can export as an XLS and copy the data over. (Its a large file)

On my second page, it reads it and this is where I need to do a few formulas.

page 1.JPG

On the "flip" column I need it to find the next auditorium of the same name, and subtract 30 minutes from the start time. I stumbled my way through the formula and got it where it will find the next auditorium of the same name and read the in time. unfortunately I cannot subtract time from it.

here is the formula I am using in "flip" column

Formula:

Please Login or Register  to view this content.

**newdoverman** · 02-21-2016, 06:54 PM

I can't tell what the column letters or row numbers are so will try and give a generic answer.
Select the column of times that you want to subtract 30 minutes from and change the format to GENERAL. If the times are real times, they will be decimal values. If they are not real times, the appearance will not change.

I think that this is the basic formula that you want to use. This will change text dates to Excel recognizable dates if they are as shown in your pictures and your previous examples. Change the references for column A to be the column where your data is. This has the data starting in row 1 so that will have to be changed to the row that the data starts.

Formula:

Please Login or Register  to view this content.

To subtract 30 minutes, subtract 1/48 from the result of the above. 30 minutes is 1/48 of a day.

**Travis.gunn** · 02-21-2016, 07:18 PM

The problem is the numbers are not imported as real times. Using A1-1/48 does not work

**newdoverman** · 02-21-2016, 07:36 PM

The formula that I gave you should convert the TEXT times to real Excel times. Make the modifications of the formula to match the actual location of the dataYou will probably require a helper column for the formula. After converting, enter the -1/48 formula where you want the results of the subtraction.

**Travis.gunn** · 02-21-2016, 07:43 PM

The attach is not working and neither is the manage uploads file. Here is via image

Popcorn Sheet.xlsx

**newdoverman** · 02-21-2016, 07:58 PM

Here is your workbook. I entered a helper column to convert the times on the Allocation worksheet. Columns E and F are what I worked with.

Importing text data to numbers!?

LinkBack

Thread Tools

Rate This Thread

Display

Importing text data to numbers!?

Re: Importing text data to numbers!?

Re: Importing text data to numbers!?

Re: Importing text data to numbers!?

Re: Importing text data to numbers!?

Re: Importing text data to numbers!?

Re: Importing text data to numbers!?

Re: Importing text data to numbers!?

Re: Importing text data to numbers!?

Re: Importing text data to numbers!?

Re: Importing text data to numbers!?

Thread Information

Users Browsing this Thread

Similar Threads

importing numbers from MYSQL truncates data

importing from csv in excel2007: decimals as text not as numbers

Importing Telephone Numbers From Text File

importing a text file with negative numbers into excel?

Retain Numbers as Text Format When Importing.

[SOLVED] Importing text data from the web

IMPORTING TEXT DATA

[SOLVED] Importing text files to Excel with big numbers

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions