I am looking to set-up a formula in Excel that will allow me to convert a part number...?

Question

I need a formula that will allow me to insert a part number (with letters and numbers) and then have it convert/switch to another part number specific to our company. For example a seven digit part number with letters and numbers. The numbers, will be converted into a number 4 higher (4 to 8, 3-7 etc.). As well, any letters in the part number need to converted to another letter a couple of spots lower. Example, a part number beginning with GH, needs to be converted into CD.

I can't seem to find something that works this way. Any help would be great.

Thanks.

Answer 1

If you have a lookup table with all of the codes then you could use the lookup function, otherwise you would need to use a combination of IIF and MID.

A better way of doing this would be to write a custom function.

Answer 2

It seems to me that what you want to do is key in the list of another companies part numbers and have a routine to convert them to your part numbers.  If so, then this VBA macro will convert each part number and insert it in the next column to the right.

Bear in mind, you are adding 4 to single digit numbers so after you reach 5 then adding 4 will give you a double digit number.  For now, this macro will go along with that and convert 6-9 as 10, 11,12, and 13.

Also bear in mind, you are subtracting 4 letters from the alphanumeric characters.  So, you cannot convert any letter before E (the 5th letter of the alphabet).  For now, I have 'errored out' letters A-D.  If you want to construct a workaround, we can.

If your source part numbers are not in Column A, modify the 'A' in Line 7 and Line 8 to your Column reference prior to copying the macro.

Open the workbook.

Copy this macro to the clipboard.

Sub Convert_Part_Nums()

Dim NewPartNum

Dim alphabet

Dim i

Dim Holder

Dim rng As Range

Set rng = Range("A1:" & Range("A65536").End(xlUp).Address(0, 0))

alphabet = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

On Error GoTo here

For Each cell In rng

cell.Select

For i = 1 To Len(ActiveCell)

If Not IsNumeric(Mid(ActiveCell, i, 1)) Then

whereat = InStrRev(alphabet, Mid(ActiveCell, i, 1), 26, 1)

convLtr = Mid(alphabet, whereat - 4, 1)

NewPartNum = NewPartNum & convLtr

Else

Holder = Mid(ActiveCell, i, 1) + 4

NewPartNum = NewPartNum & Holder

End If

convLtr = ""

Holder = ""

Next i

ActiveCell.Offset(0, 1).Value = NewPartNum

NewPartNum = ""

Next cell

Exit Sub

here:

MsgBox "Cannot convert to a letter before 'A'" ,  _

vbOKOnly, "Invalid"

End Sub

Press ALT + F11

Insert  >  Module

Paste the macro into the Module space to the right.

Close back to Excel

Go to Tools  >  Macro  >  Macros

Click on the macro to highlight it, if it is not already highlighted.

Click:  Options

Select a letter to be used as a keyboard shortcut.

Close back to Excel.

To run the macro, press CTRL + your shortcut letter.

Answer 3

You will need to look into "IF" statements. Based on what you've explalined so far, it's a bit tough to create a specific formula, but again, I'm almost certain "IF" statements will do the job for you.

Just hit the F1 key when you're in Excel. That will bring up the Help Section which can tell you all about "IF Statements".

Answer 4

from what I gather you have a list of your company part #'s together with a list of the converted part #'s somewhere.  And when someone enters a part #, you want to get the converted # of it.  If that's the case, then a vlookup will work.

lets say your one part # is in column A and the corresponding converted numbers are in column B

and you enter your original part # in C1.

in D1 enter this formula

=VLOOKUP(C1, A1:B100, 2, FALSE)

(assuming your parts list is in A1 thru B100).

This will look up the part you entered (C1) in the your list of parts. When it finds a match in column A, it will return the results from the 2nd column (col. B).  The "false" just tells excel to find an exact match