• 0

Formatting Excel cells as a serial number


Question

Hello all,

I was wondering if any of you have ever used conditional formatting in excel to format a cell as a serial number.

for example, lets say I have a series of cells

x1s43dlduenaa34532neow234

x1s43dada3daa345adneow239

x1s43dada3daa34532ne0ru38

x1s43dadasdyr34532neow237

x1s43dkf2kfuef4532neow236

x1s43dnf29daa34532neow235

and I want excel to automagically convert these cells to be this structure:

XXXXX-XXXXX-XXXXX-XXXXX-XXXXX

How would I accomplish this?

Yes, all of these serial numbers my company owns, no piracy here. Just having dozens of boxes of serial numbers laying around my server room isn't very intelligent or organized.

EDIT: Also, for bonus internet points, how do I force all letters to be capital?!

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

In a module:

Public Function converttoserial(myCell As Range) As String

Dim x As String

x = myCell.Value

converttoserial = UCase(Left(x, 5) + "-" + Mid(x, 6, 5) + "-" + Mid(x, 11, 5) + "-" + Mid(x, 16, 5) + "-" + Mid(x, 21, 5))

End Function

then in a cell the usage is:

=converttoserial(A2)

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.