# Importing text data to numbers!?

1. ## Importing text data to numbers!?

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!  Register To Reply

2. ## Re: Importing text data to numbers!?

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  Register To Reply

3. ## Re: Importing text data to numbers!?

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.`  Register To Reply

4. ## Re: Importing text data to numbers!?

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  Register To Reply

5. ## Re: Importing text data to numbers!?

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.`  Register To Reply

6. ## Re: Importing text data to numbers!?

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.`  Register To Reply

7. ## Re: Importing text data to numbers!?

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.  Register To Reply

8. ## Re: Importing text data to numbers!?

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

9. ## Re: Importing text data to numbers!?

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.  Register To Reply

10. ## Re: Importing text data to numbers!?

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

Popcorn Sheet.xlsx  Register To Reply

11. ## Re: Importing text data to numbers!?

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.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 