Dynamic Mapping Account Number

[yuanming1992]

Hi guys,

Appreciate your help first.

 I have two excel files and one have account number like:

account number
1234*
155*
178*
1333*
22*

and another one have:

account number
12345

12346

12348
1556

1559

1552
1787
13335
224

226

227

these are just few examples, I have way more than what I shown here.

How do i map below 

12345, 12346, 12348 to 1234*,

1556,1559, 1552 to 155*,

13335, to 1333*,

224,226,227 to 22*?

you can do it in opposite way like:

 22* to 224,226,227

I can use formula tool to manually change that last number to * but I kind want it more dynamic because I have so many numbers and people might add number in there.

please be more dynamic.

Thank you so much for you help.

[SPetrie]

Is something like this helpful? I used a dynamic replace to get the results. Since one of the files has accounts in a format that is close to a regex formula, I used that as a tool to see if it matches or not.