Match field from 2 tables with different format

[jorditorras]

Hi Gurus,

I have a field that appears in two different tables (same field but different names) and I cannot match them because they appear like:

Table 1:

12345

Table 2:

000000012345

And when I see the fields in a table (in the App), the value of table 1 appears in the left side of the cell, and the one of table 2 appears in the right side. 

How can I unify the format in order to match the values? (by the way, table 1 contain more records than table 2, and some of them have a format like 1/ABD-1 

Any idea? I've tried to use Num#() in both of them but it dosn't work...

[Vegar]

Tricky when you need to preserver both numbers and strings such as '1/ABC-1'.

You could try using an expression like this:

ALT(Num(Num#([YourField])),[YourField])

[jorditorras]

It's not working... But in any case, the fields like 1/ABC-1 can be ignored as they are not rellevant.

Thanks for your help!

[jorditorras · Accepted solution]

 Hi, I've found a solution. This is what I've applyed:

,If(IsNum(Replace(LTrim(Replace(USR00,'0',' ')),' ','0')), Replace(LTrim(Replace(USR00,'0',' ')),' ','0'), USR00) as [Measure code]