Lou Pointspreads
New Member
- Joined
- Oct 24, 2018
- Messages
- 10
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
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