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

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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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([[COLOR=#ff0000]ID[/COLOR]],1) & "-" & Mid([[COLOR=#ff0000]ID[/COLOR]],3,3)*1 & "-" & Right([[COLOR=#ff0000]ID[/COLOR]],4)*1
 
Upvote 0
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 :(
 
Upvote 0
I'm also a little unclear on the logic relating the two numbers.

Are you able to post some more examples?
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top