convert cells

[wwarneke]

Hello Excel guru's, I need to convert the text data in an excel sheet to a fixed length number.
For example:

Emp#
ALY/5632 = 41021345632

In this example, output always starts with "4".
A=10, Z=35 etc
/ is removed

can I do this in one extra column?

Thanks!

 

[jtakw]

So in my sample in Post #8 , for A3, A4, A5, those will Not result in 11 digits converted, due to the "numbers" within the first 3 characters, how do you want those "numbers" handled?

 

[wwarneke]

Pad with "0" for 2 digits.

 

[jtakw]

Pad with "0" for 2 digits.

You mean like this?

Book1
	A	B	C
1	Emp#
2	ALY/5632	41021345632	41021345632
3	F66/1019	41560601019	41560601019
4	F66/0098	41560600098	41560600098
5	J3N/1175	41930231175	41930231175
6	123/4567	41020304567	41020304567
7	F06/0098	41500600098	41500600098
8
9		Result is Text	Result is Number
Sheet146

Cell Formulas
Range	Formula
B2	=4&IF(ISNUMBER(LEFT(A2,1)+0),LEFT(A2,1)&0,CODE(A2)-55)&IF(ISNUMBER(MID(A2,2,1)+0),MID(A2,2,1)&0,CODE(MID(A2,2,1))-55)&IF(ISNUMBER(MID(A2,3,1)+0),MID(A2,3,1)&0,CODE(MID(A2,3,1))-55)&RIGHT(A2,4)
C2	=(4&IF(ISNUMBER(LEFT(A2,1)+0),LEFT(A2,1)&0,CODE(A2)-55)&IF(ISNUMBER(MID(A2,2,1)+0),MID(A2,2,1)&0,CODE(MID(A2,2,1))-55)&IF(ISNUMBER(MID(A2,3,1)+0),MID(A2,3,1)&0,CODE(MID(A2,3,1))-55)&RIGHT(A2,4))+0

 

[wwarneke]

Almost but I was thinking F66 = 150606

 

[Peter_SSs]

If you are using Office 365, try this.

Edit: I've added a marginally shorter formula in column C.

Excel Workbook
	A	B	C
1	Emp#
2	ALY/5632	41021345632	41021345632
3	F66/1019	41506061019	41506061019
4	F66/0098	41506060098	41506060098
5	J3N/1175	41903231175	41903231175
6	123/4567	40102034567	40102034567
7	F06/0098	41500060098	41500060098
Convert

 

[jtakw]

Almost but I was thinking F66 = 150606

Well, you didn't specify....

Book1
	A	B	C
1	Emp#
2	ALY/5632	41021345632	41021345632
3	F66/1019	41506061019	41506061019
4	F66/0098	41506060098	41506060098
5	J3N/1175	41903231175	41903231175
6	123/4567	40102034567	40102034567
7	F06/0098	41500060098	41500060098
8
9		Result is Text	Result is Number
Sheet146

Cell Formulas
Range	Formula
B2	=4&IF(ISNUMBER(LEFT(A2,1)+0),0&LEFT(A2,1),CODE(A2)-55)&IF(ISNUMBER(MID(A2,2,1)+0),0&MID(A2,2,1),CODE(MID(A2,2,1))-55)&IF(ISNUMBER(MID(A2,3,1)+0),0&MID(A2,3,1),CODE(MID(A2,3,1))-55)&RIGHT(A2,4)
C2	=(4&IF(ISNUMBER(LEFT(A2,1)+0),0&LEFT(A2,1),CODE(A2)-55)&IF(ISNUMBER(MID(A2,2,1)+0),0&MID(A2,2,1),CODE(MID(A2,2,1))-55)&IF(ISNUMBER(MID(A2,3,1)+0),0&MID(A2,3,1),CODE(MID(A2,3,1))-55)&RIGHT(A2,4))+0

 

[Peter_SSs]

If you don't have the CONCAT function for the post 15 suggestion, here is a slightly shortened version of jtakw's latest B2 suggestion.

Excel Workbook
	A	B
1	Emp#
2	ALY/5632	41021345632
3	F66/1019	41506061019
4	F66/0098	41506060098
5	J3N/1175	41903231175
6	123/4567	40102034567
7	F06/0098	41500060098
Convert (2)

 

[Rick Rothstein]

For those who might want to do something like this using a UDF (user defined function), here one that will work...

[table="width: 500"]
[tr]
	[td]Function Emp(ID As String) As String
  If Len(ID) Then Emp = 4 & Join(Evaluate("IF({1},TEXT(FIND(MID(""" & ID & """,COLUMN(A:C),1),""0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"")-1,""00""))"), "") & Right(ID, 4)
End Function[/td]
[/tr]
[/table]

 

[Peter_SSs]

Another (shorter) CONCAT formula. This one is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

Excel Workbook
	A	B
1	Emp#
2	ALY/5632	41021345632
3	F66/1019	41506061019
4	F66/0098	41506060098
5	J3N/1175	41903231175
6	123/4567	40102034567
7	F06/0098	41500060098
Convert (3)

 

[lrobbo314]

Turn to the dark side... Use VBA!