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.

output will be account 1 and account 2.

Thank you so much for you help.

[Bren_Spill]

@yuanming1992 - see solution attached

[Bren_Spill · Accepted solution]

@yuanming1992 - correction

[yuanming1992]

Is this the same logic for 

opposite way like:

 22* to 224,226,227

Thank you so much for your help.

[Bren_Spill]

@yuanming1992 - I don't think it will work the other way. For that to work, you would need to know what number to replace the * with. If the last digit in the full account number is contently changing, I don't see a way to make that dynamic.

If this works for you, please accept as solution. Thanks!