Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The attached workbook represents an export of data that I receive to compile personnel travel reporting. I actually receive two different exports both in MS Excel. The first export is a current travel report while the second is a future travel report that has names and travel itineraries for personnel traveling some time in the future (starting tomorrow perhaps). Neither export has unique identifiers other than names. I need to merge the two exports to build my reporting off of, but the data needs to be fixed first.

    Problem number one is cell B2. Texas(SAN ANTONIO) I would like the names of States and Cities to be CAPITALIZED. Is there a function for doing that?

    The next problem is doing something useful with the date text string in C2. 03-OCT-10 - 02-NOV-10. I can break it up using text to columns, but then formatting is not as smooth as I want. What is the optimal way of separating the data into a start and end date column in a proper date format that can be sorted and then rejoined again in different string?

    Your help is always appreciated.

    Amy
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hello Amy - Nothing was attached to your message.

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Calgary, Alberta, Canada
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The attached might offer some formula suggestions that may work for you.
    Attached Files Attached Files

  4. #4
    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
    Name:
    =PROPER(A2)

    State/City:
    =PROPER(B2)

    StartDate [Format as desired]:
    =DATEVALUE(LEFT(C2,9))

    EndDate [Format as desired]:
    =DATEVALUE(RIGHT(C2,9))

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Peter/Steve,
    Thank you. I integrated some changes with what you provided. I am trying to separate the city out of the parenthesis as another transformation step. I got close, but take a look at column F in the attached workbook.

    Thanks for you help.

    Amy[attachment=90226:Travel Data Export.xlsx]
    Attached Files Attached Files

  6. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    Calgary, Alberta, Canada
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    How about this for column F

    =SUBSTITUTE(MID(B2,FIND("(",B2,1)+1,LEN(B2)),")"," ")

  7. #7
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Peter,
    That worked brilliantly. I think my pieces are coming together and I will be able to move this solution to the MS Access platform.

    You guys are great.

    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
  •