+ Reply to Thread
Results 1 to 9 of 9

TRIM function or CHAR function? Or something I cannot figure out!

  1. #1
    Registered User
    Join Date
    01-20-2017
    Location
    Korea
    MS-Off Ver
    California
    Posts
    3

    TRIM function or CHAR function? Or something I cannot figure out!

    Hello,
    I have thousands of row of data containing time. I need to know the length of time for a certain task. I have the start and finish times in two separate columns (Column A and Column B) but for whatever reason, the start time is not being recognized as time, unless I manually go into each cell in column A and delete a leading space, that doesn't even seem to be there. Please see attached.
    If I place my cursor in front of the first number of the start time and press backspace, then enter, the text gets recognized as a time, and then the calculation is complete in column C. But unless I manually go in and press backspace for each start time, the length of each task cannot be calculated. I have tried everything I know and cannot seem to figure this out. Could someone please assist me?
    I have tried TRIM, SUBSTITUTE, etc.
    Attached Files Attached Files

  2. #2
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: TRIM function or CHAR function? Or something I cannot figure out!

    Formatting problem. While enter data, how to entering data in sheet.


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  3. #3
    Registered User
    Join Date
    01-20-2017
    Location
    Korea
    MS-Off Ver
    California
    Posts
    3

    Re: TRIM function or CHAR function? Or something I cannot figure out!

    Thank you for your reply. Could you please clarify where the formatting issue is? I looked into formatting but could not seem to figure it out. Thank you for your help!!

  4. #4
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: TRIM function or CHAR function? Or something I cannot figure out!

    Hi
    Some data in column A has a extra char
    Try this in C2 and copy down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: TRIM function or CHAR function? Or something I cannot figure out!

    Jose : Not work (I trying using excel2007)
    hklen : Can you explain the said data you enter manually or from any other sorce & copy paste into excel sheet.

  6. #6
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Microsoft Office 2013
    Posts
    3,215

    Re: TRIM function or CHAR function? Or something I cannot figure out!

    Jose : Not work (I trying using excel2007)
    hklen : Can you explain the said data you enter manually or from any other sorce & copy paste into excel sheet.

  7. #7
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: TRIM function or CHAR function? Or something I cannot figure out!

    Try this

    PHP Code: 
    =VALUE(B2)-IF(CODE(MID(A2,1,1))=63,VALUE(MID(A2,2,9)),VALUE(A2)) 
    see the file
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    06-12-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    86

    Re: TRIM function or CHAR function? Or something I cannot figure out!

    Please find attached sheet with formula. Hope this may resolve your issues

    Formula to replace cell A
    TIME(IF((FIND(":",A25,1))=3,MID(A25,2,1),MID(A25,2,2)),MID(A25,(FIND(":",A25,1)+1),2),0)

    Formula to replace Cell B
    TIME((IF((FIND(":",B25,1))=2,LEFT(B25,1),LEFT(B25,2))),(MID(B25,(FIND(":",B25,1)+1),2)),0)
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    01-20-2017
    Location
    Korea
    MS-Off Ver
    California
    Posts
    3

    Re: TRIM function or CHAR function? Or something I cannot figure out!

    You guys are amazing! I apologize for the late late reply! I was away this past weekend. Thank you Kamal and to Jose for your quick reply and help. It worked!! Thank you!!!

+ 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. CHAR in =IF function.
    By Fatedela in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-22-2016, 01:30 AM
  2. Replies: 4
    Last Post: 10-08-2013, 05:10 PM
  3. [SOLVED] Function to take text from x:th char to next n char
    By Temporary-Failure in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-07-2013, 02:19 AM
  4. Trying to trim a trailing space char and the Trim function isn't working
    By Psychochook in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-30-2007, 11:28 PM
  5. [SOLVED] string/char CONTAINS function?
    By jim sturtz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-31-2006, 02:20 PM
  6. Replies: 3
    Last Post: 12-29-2005, 11:10 PM
  7. [SOLVED] How can I use CHAR() function in VB?
    By Basil in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-23-2005, 01:05 PM

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