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!

 

[mikerickson]

How does A=10 and Z=35?
What do L and Y get replaced with?
Where is the letter to number conversion chart?
How does ALY get translated to its number? Cocatenate the numerals? or add them? or something else?

 

[jtakw]

Hi,

If your Employee # are Always in the format in your sample:

Book1
	A	B	C
1	Emp#
2	ALY/5632 = 41021345632	41021345632	41021345632
Sheet146

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

B2 formula results in Text.
C2 formula converts results to Real Numbers.

If you have Employee #s in different formats, please show all possible formats.

 

[wwarneke]

My conversion chart will be A=10, B=11, C=12 etc up to Z=35
but that is my question how to convert...

 

[wwarneke]

Thanks!
Employee #s are always in this format
F66/1019
F66/0098
J3N/1175

Last 4 digits after / are always numeric

 

[jtakw]

Thanks!
Employee #s are always in this format
F66/1019
F66/0098
J3N/1175

Last 4 digits after / are always numeric

These are Different than the sample in your OP, these are Mixed Alpha/Numeric for the Left 3 characters, your OP was All Alphabetic.

Will post back with updated formula.

 

[lrobbo314]

These are Different than the sample in your OP, these are Mixed Alpha/Numeric for the Left 3 characters, your OP was All Alphabetic.

Agreed, that will throw my formula for a loop too. But, just for fun, here's what I came up with.

=4&SUMPRODUCT((CODE(MID(LEFT(A1,FIND("/",A1)-1),ROW(INDIRECT("1:"&FIND("/",A1)-1)),1))-55)*({10000;100;1}))&RIGHT(A1,LEN(A1)-FIND("/",A1))

 

[jtakw]

Assuming the "Numbers" within the Left 3 characters need No conversion, Only Letters gets converted:

Book1
	A	B	C
1	Emp#
2	ALY/5632	41021345632	41021345632
3	F66/1019	415661019	415661019
4	F66/0098	415660098	415660098
5	J3N/1175	4193231175	4193231175
Sheet146

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

B2 formula results in Text.
C2 formula results in Real Numbers.

 

[lrobbo314]

And a VBA solution.

Function Emp(ID As String) As String
Dim SP()    As String: SP = Split(ID, "/")
Dim P1      As String: P1 = SP(0)
Dim P2      As String: P2 = SP(1)
Dim Ch      As String
Dim Result  As String


Result = 4


For i = 1 To Len(P1)
    Ch = Mid(P1, i, 1)
    If IsNumeric(Ch) Then
        Result = Result & Format(Ch, "00")
    Else
        Result = Result & Asc(Ch) - 55
    End If
Next i


Result = Result & P2


Emp = Result


End Function

 

[wwarneke]

Thanks for the help/replies!
Sorry for the lack of clarity in my op, for some reason the forum was hanging really bad in my browser very difficult to type...
Now working better in chrome.
My goal is to take this Emp. list report from QB and convert the emp #'s to an 11 digit number beginning with 4. Will be used to create barcodes for and Emp discount card.
I always used to do stuff like this by convert to csv and use parseOmatic but doing in excel is good experience.
Thanks again