how do i merge down a sum of formulas into one function

[jorgefilemon]

ok i have written down this

=LEFT(A1,3)&MID(A1,FIND(" ",A1)+1,3)&MID(A1, FIND(" ",A1,FIND(" ",A1)+1)+1, 2)&RIGHT(A1,2)&TEXT(MONTH(TODAY()),"00")&RIGHT(YEAR(TODAY()),2)

i put these formulas together to generate SKU for my store.

I wanna turn this into a function named SKU(), for easy access in excel, how do i do this?

 

[Joe4]

Welcome to the Board!

Can you give us an example of an entry into A1 so we have something to test?

 

[jorgefilemon]

OK cool so I have a shoe store, so I have this:

brand model color size

A1 = BARALDI 4505666 BLACK 20

The formulas generates this:

BAR450BL200514

<colgroup><col></colgroup><tbody>
</tbody>

(the last four digits in the SKU are the date (month,year)when the product is uploaded to the system)

 

[Joe4]

OK. Here you go. I split each piece to its on separate line in the VB code to see more easily what each part does:

Function SKU(myEntry As String) As String

    SKU = Left(myEntry, 3) & _
          Mid(myEntry, InStr(myEntry, " ") + 1, 3) & _
          Mid(myEntry, InStr(InStr(myEntry, " ") + 1, myEntry, " ") + 1, 2) & _
          Right(myEntry, 2) & _
          Format(Date, "mmyy")
        
End Function

So, on an Excel sheet, if the item was in cell A1, you would just use:
=SKU(A1)

 

[Rick Rothstein]

Here is another UDF that should also work...

Function SKU(myEntry As String) As String
  SKU = Application.Replace(myEntry, 4, InStr(myEntry, " ") - 3, "")
  SKU = Left(Application.Replace(SKU, 7, InStr(SKU, " ") - 6, ""), 8) & Split(myEntry)(3) & Format(Date, "mmyy")
End Function

 

[jorgefilemon]

hi guys, thanks for your help, it's working great!.

how do I save my costum function so it's always shown in the functions menu in every workbook?

I read microsoft instructions, and I did what they said, but i just can't make it shown in the "more functions…" menu in any other workbook. I'm on mac btw.





MICROSOFT INSTRUCTIONS
An easier way to make your custom functions available at all times is to store them in a separate workbook and then save that workbook as an add-in (an XLA file) in your XLStart folder. (The XLStart folder is a subfolder of the folder containing your Excel files. When you start Excel, the program opens any documents it finds in XLStart.) To save a workbook as an add-in, choose File, Save As (or File, Save). Then choose Microsoft Excel Add-in from the Files Of Typelist.
If your user-defined functions are stored in an XLA file that is present in memory, you don't have to specify the name of that file when you call a function. If the XLA file is saved in your XLStart folder, it will be present in memory whenever you run Excel.

 

[Rick Rothstein]

I read microsoft instructions, and I did what they said, but i just can't make it shown in the "more functions…" menu in any other workbook. I'm on mac btw.

I do not have access to a Mac, so I have no way to test anything I might come up with (unfortunately, there are differences between the PC and Mac worlds). Wait to see if someone with knowledge of the Mac world comes along... if not, start a new thread and mention in the thread's title that you are seeking a Mac solution so the "right" people can react to your question.

 

[Joe4]

Note that if you save it to your Personal Macro Workbook, it will always be available to use every time you have Excel open on your computer.
See: How to create a Personal Macro Workbook in Excel for Mac