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

[Lou Pointspreads]

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.[TABLE="width: 500"]
<tbody>[TR]
[TD]<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>4 13001353[/TD]
[TD]=(+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))))[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[stumac]

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):

Left([[COLOR=#ff0000]ID[/COLOR]],1) & "-" & Mid([[COLOR=#ff0000]ID[/COLOR]],3,3)*1 & "-" & Right([[COLOR=#ff0000]ID[/COLOR]],4)*1

 

[xenou]

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

 

[JonXL]

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

Are you able to post some more examples?