Creating consistent login IDs with one formula

[jodeyes]

I get data from various sources and have to create user login IDs that are consistent. I would like help getting one formula (not a macro) that will create the correct ID for all of the different scenarios. Here are more details:

User ID requirements:

• All numbers (no letters or other characters)
• 8 digits
• First 2 digits are the site Location ID
• Other 6 digits are taken from the Employee ID
• When EmpID is less than 6 digits, we fill with zeroes to make up the 8 digits
• When EmpID is more than 6 digits, we take the right-most digits
• We ignore dashes and letters and follow the above rules.

I have formulas for all of these scenarios, but don't know how to combine them into one.

Location ID: 32
Employee ID What User ID should be
123456 32123456
8-928-664 32928664
8-951-1443 32511443
9771 32009771
10029 32010029
1384991 32384991
CCI36874 32036874

(Hope this is understandable - I couldn't get the tables to work right and cannot attach.)

Thank you in advance!

 

[Eric W]

If you have the CONCAT function in your version of Excel, then maybe:

	A	B
1	Location ID	32
2	123456	32123456
3	8-928-664	32928664
4	8-951-1443	32511443
5	9771	32009771
6	10029	32010029
7	1384991	32384991
8	CCI36874	32036874

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Array Formulas Cell Formula B2 {=$B$1&RIGHT(TEXT(CONCAT(IFERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0,""))+0,"000000"),6)} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[lrobbo314]

If you don't have 'Concat' available, then this should do it. Use a helper column. This formula can be used to extract the numerical portion of the Employee ID in Column A. I put this formula in E1. This one is an array formula so, 'Ctrl+Shift+Enter' after pasting the formula.

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))* ROW(INDIRECT("1:"&LEN(A1))),0),ROW(INDIRECT("1:"&LEN(A1))))+1,1)*10^ROW(INDIRECT("1:"&LEN(A1)))/10)

Then I put the following formula in F1 to generate the User ID.

=32&IF(LEN(E1)<6,REPT("0",6-LEN(E1))&E1,RIGHT(E1,6))

Then you can just copy the formulas down.

 

[jtakw]

Hi,

If the possible Alphabets/Letters in the Employee ID are Always on the Left, then this would work:

Book1
	A	B
1	Location ID	32
2	Employee ID	User ID
3	123456	32123456
4	8-928-664	32928664
5	8-951-1443	32511443
6	9771	32009771
7	10029	32010029
8	1384991	32384991
9	CCI36874	32036874
Sheet161

Cell Formulas
Range	Formula
B3	=B$1&TEXT(RIGHT(SUBSTITUTE(MID(A3,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A3&"0123456789")),255),"-",""),6),"000000")

 

[jodeyes]

This worked great for me, jtakw. Thanks so much!

 

[jodeyes]

Thank you, Eric W. My audience cannot handle the Ctrl+Shift+Enter, but I am going to note this for a future project.

 

[jodeyes]

Thank you, lrobbo314. My audience cannot handle the Ctrl+Shift+Enter, but I am going to note this for a future project.

 

[jtakw]

This worked great for me, jtakw. Thanks so much!

You're welcome.