+ Reply to Thread
Results 1 to 11 of 11

Importing text data to numbers!?

  1. #1
    Registered User
    Join Date
    02-20-2016
    Location
    Seattle
    MS-Off Ver
    2013
    Posts
    5

    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!

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex
    MS-Off Ver
    365
    Posts
    5,878

    Re: Importing text data to numbers!?

    Assuming your data is in cell A1, the below formula will subtract 30 minutes from it:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    BSB

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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: copy to clipboard
    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: copy to clipboard
    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: copy to clipboard
    Please Login or Register  to view this content.


    Use this to subtract 30 minutes from either of the times calculated above
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Registered User
    Join Date
    02-20-2016
    Location
    Seattle
    MS-Off Ver
    2013
    Posts
    5

    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

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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: copy to clipboard
    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: copy to clipboard
    Please Login or Register  to view this content.

    Then in C1 enter this and fill down to subtract 30 minutes
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    02-20-2016
    Location
    Seattle
    MS-Off Ver
    2013
    Posts
    5

    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: copy to clipboard
    Please Login or Register  to view this content.

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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: copy to clipboard
    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.

  8. #8
    Registered User
    Join Date
    02-20-2016
    Location
    Seattle
    MS-Off Ver
    2013
    Posts
    5

    Re: Importing text data to numbers!?

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

  9. #9
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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.

  10. #10
    Registered User
    Join Date
    02-20-2016
    Location
    Seattle
    MS-Off Ver
    2013
    Posts
    5

    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

  11. #11
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    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.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. importing numbers from MYSQL truncates data
    By jkzfixme in forum Excel General
    Replies: 0
    Last Post: 08-04-2010, 03:34 PM
  2. Replies: 1
    Last Post: 08-31-2009, 08:40 AM
  3. Importing Telephone Numbers From Text File
    By hagadol in forum Excel General
    Replies: 2
    Last Post: 05-29-2008, 10:13 AM
  4. importing a text file with negative numbers into excel?
    By jstnvndn in forum Excel General
    Replies: 1
    Last Post: 09-19-2006, 09:45 AM
  5. [SOLVED] Retain Numbers as Text Format When Importing.
    By xardoz in forum Excel General
    Replies: 2
    Last Post: 06-20-2006, 12:20 PM
  6. Importing text data from the web
    By Lawood in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-06-2005, 04:10 PM
  7. [SOLVED] IMPORTING TEXT DATA
    By Bill C in forum Excel General
    Replies: 1
    Last Post: 05-12-2005, 06:06 AM
  8. Importing text files to Excel with big numbers
    By Orjan in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-17-2005, 04:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1