Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi guys,
    The attached workbook has some formulas (columns highlighted in blue). One is wrong (Column C). Another one is probably not the most optimal solution (Column I).

    The unhighlighted columns represents data that I receive from an export. The highlighted columns are helping me to make the data more usable.

    =DATEVALUE(TEXT(((MID(H2,1,(LEN(H2)-1-LEN(RIGHT(H2,FIND(" ",H2))))))&", "&(RIGHT(H2,FIND(" ",H2)))),"dd-mmm-yy"))

    An help in correcting and validating is always appreciated.

    Thanks
    Amy
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    In C2 you can something like
    =RIGHT(A2,LEN(A2)-FIND(",",A2)-1)
    or
    =TRIM(MID(A2,FIND(",",A2)+1,LEN(A2)))

    Find counts from the left not the right...

    in I2
    =DATEVALUE(SUBSTITUTE(H2," ",", ",2))

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Steve,
    This worked great. Thank you.

    Amy

Posting Permissions

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