# 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: 0 Likes: 0

1. ## 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  Reply With Quote

2. ## 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`  Reply With Quote

3. ## 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   Reply With Quote

4. ## 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?  Reply With Quote

## User Tag List

#### Tags for this Thread

convert, digits, format, text #### Posting Permissions

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