How to translate an Excel text string if formula to a calculated field in access to match another tables format and be able to relate the tables
Results 1 to 4 of 4

Thread: How to translate an Excel text string if formula to a calculated field in access to match another tables format and be able to relate the tables
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2018
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to translate an Excel text string if formula to a calculated field in access to match another tables format and be able to relate the tables

    Hello wonderful Access Aficionados,

    So i have this Db, which has a set of 10 numbers in the following format 1-111-1111 and the second table has the same information but the system that it comes from, does not handle "-" dashes or empty spaces which end up looking like "1 011101111" which is the first digit followed by a space 1" ", then the remaining digits.

    To make matters a little more complicated, it is possible that the three middle digits do not use the three digits if it is a number from 1-9, or two digits if 1-99 in which case the data for 1-1-1111 would read: "1000101111" or for 1-10-1111 would read 1001001111. And again this can happen for the last 4 digits as well.

    So i did some research, and tinkered with the following excel formula, which works well with the three middle digits, but wasn't able to nail the last four digits, i think i was only able to pick up if the first character of the last four digits is a zero skip and return the next three digits, where i wasnt' able to do this again in a chain in case the second of the last four digits was a zero as well.

    =(+LEFT(A3,1))&"-"&(+IF(AND(A3=(MID(A3,3,1)="0"),A3=(MID(A3,4,1)="0")),MID(A3,5,1),+IFS(MID(A3&" ",3,1)="0",MID(A3&" ",4,2),MID(A3&" ",3,1)<>0,MID(A3&" ",3,3))))&"-"&(+IF(AND(A3=(MID(A3,7,1)="0"),A3=(MID(A3,8,1)="0")),MID(A3,9,2),+IFS(MID(A3&" ",7,1)="0",MID(A3&" ",8,3),MID(A3&" ",7,1)<>0,MID(A3&" ",7,4))))

    Where A3 would be the number in the horrible format we are trying to convert, in order to be able to match it to the table with the correct format.
    4 13001353 =(+LEFT(A3,1))&"-"&(+IF(AND(A3=(MID(A3,3,1)="0"),A3=(MID(A3,4,1)="0")),MID(A3,5,1),+IFS(MID(A3&" ",3,1)="0",MID(A3&" ",4,2),MID(A3&" ",3,1)<>0,MID(A3&" ",3,3))))&"-"&(+IF(AND(A3=(MID(A3,7,1)="0"),A3=(MID(A3,8,1)="0")),MID(A3,9,2),+IFS(MID(A3&" ",7,1)="0",MID(A3&" ",8,3),MID(A3&" ",7,1)<>0,MID(A3&" ",7,4))))

    The correct format for this would be 4-130-1353

    And excel pretty much gets it right, unless the second and third digits happen to be zeros which would return something like 4-130-030 or 4-130-003 for the values 4-130-30 or 4-130-3 (anyways, i know this is one if chain away, just gotta keep trying)

    And before anyone points me in the direction towards the excel forum, i would like to ask what would be the best way to solve this issue in access? I tried tinkering with a calculated field and this formula, but wasn't able to get it working.

    Read some more and i understand that as much as they are similar formulas, you need to word or phrase it differently in case of & to join strings of characters and to add the " - " sign between numbers.

    At the end of the day, i would like to be able to modify the incorrect number format within access, and automate it so it would generate a new column with the correct format within the same table so that it may then be related to table 2 with the correct number format.

    Any thoughts, tips or links with recommended readings would be greatly appreciated.

    Thanks for the read if you made it this far!

    Cheers,

    Lou

  2. #2
    Board Regular
    Join Date
    Jul 2010
    Posts
    425
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to translate an Excel text string if formula to a calculated field in access to match another tables format and be able to relate the tables

    the use of Calculated fields is generally frowned upon - there is cases where they dont update as well as other known issues (check google out for that). However, there has been times where I have used them for reasons similar to yours with no problems.

    try (replacing ID with your field name):

    Code:
    Left([ID],1) & "-" & Mid([ID],3,3)*1 & "-" & Right([ID],4)*1

  3. #3
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,425
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to translate an Excel text string if formula to a calculated field in access to match another tables format and be able to relate the tables

    So i have this Db, which has a set of 10 numbers in the following format 1-111-1111 and the second table has the same information but the system that it comes from, does not handle "-" dashes or empty spaces which end up looking like "1 011101111" which is the first digit followed by a space 1" ", then the remaining digits.

    To make matters a little more complicated, it is possible that the three middle digits do not use the three digits if it is a number from 1-9, or two digits if 1-99 in which case the data for 1-1-1111 would read: "1000101111" or for 1-10-1111 would read 1001001111. And again this can happen for the last 4 digits as well.
    In paragraph one you say there is a space after the first 1.
    In paragraph two there are no spaces in the examples.

    So it's unclear what the rules are here

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  4. #4
    Board Regular JonXL's Avatar
    Join Date
    Feb 2018
    Posts
    205
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to translate an Excel text string if formula to a calculated field in access to match another tables format and be able to relate the tables

    I'm also a little unclear on the logic relating the two numbers.

    Are you able to post some more examples?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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