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
Location ID
8-928-664
8-951-1443
CCI36874

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]32[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]123456[/TD]
[TD="align: right"]32123456[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]32928664[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]32511443[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]9771[/TD]
[TD="align: right"]32009771[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]10029[/TD]
[TD="align: right"]32010029[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1384991[/TD]
[TD="align: right"]32384991[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]32036874[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]{=$B$1&RIGHT(TEXT(CONCAT(IFERROR(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0,""))+0,"000000"),6)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.