Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2009
    Posts
    6

    Unanswered: Reformat in a query

    I would like to re-format a text string form this format;

    100012503222W400

    to this format;

    00/01-25-032-22W4/0

    It requires droping the first number, then inserting the slashes and dashes and removing the secound last number.

    Any ideas?

    Thank you in advance.
    Last edited by bigd2; 11-05-09 at 14:59.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Look at the Mid() function and concatenation:

    NewField: Mid(...) & "/" & Mid(...) & ...
    Paul

  3. #3
    Join Date
    May 2009
    Posts
    258
    Hopefully I understood correctly, in that you want the first character stripped and do not want to use the second-to-last character. If so, you can use the following in the query:
    Code:
    Format(Mid(theString,2,Len(theString)-3)&Right(theString,1),'@@/@@-@@-@@@-@@@@/@')
    So in your example string, the characters used are bold:
    100012503222W400

    Ax

  4. #4
    Join Date
    Nov 2009
    Posts
    6
    Thanks Ax238 that worked perfectly. Your a friggin rockstar.

    Could you show me how to format them back as I sometimes need to go either way depending on my data source? That would mean striping the slashes and dashes, and inserting a "1" at the begining and a "0" at the secound last position.

    Cheers

  5. #5
    Join Date
    May 2009
    Posts
    258
    Good deal! To get the string back, just use the following:
    Code:
    '1'&Replace(Replace(Replace(theString,'/','',1,1),'-',''),'/','0')
    Ax

  6. #6
    Join Date
    Nov 2009
    Posts
    6
    Work perfect. Thanks again Ax238 - you rock dude!

  7. #7
    Join Date
    May 2009
    Posts
    258
    Awesome, glad I could help!

Posting Permissions

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