How can I make Excel ignore special characters and spaces when taking data from another cell?

Question

I have an Excel workbook which is used to produce quotations for customers. It automatically generates a unique quotation number based on the salesperson's name, the name of the company receiving the quotation, the date, and the issue number (if two separate quotations are being made by the same salesperson, for the same company, on the same day, the first will have the issue number 01, the second will have the issue number 02, etc.)

Filled with dummy information, a typical quotation number will look like this:

JS-ABC-05052016-01

The 'JS' is the initials of the salesperson (John Smith.) The 'ABC' is the first three characters of the company name (ABCompany.) The '05052016' is today's date, and '01' is the issue number. This is all information entered by the salesperson.

The formula used in the cell which generates and displays the quotation number is:

=UPPER(LEFT(P4,1)&LEFT(P5,1)&"-"&LEFT(D11,3)&"-"&LEFT(K6,2)&MID(K6,4,2)&MID(K6,7,4)&"-"&D5)

The above formula is taking pieces of information that are entered in cells, and compiling them to generate the quotation number.

There is an issue, however, when the first three characters of the company name contains a blank space, or a special character. For example, a company name of 'A. B. Company' would generate the following quotation number:

JS-A. -05052016-01

Another example is that a company name of 'A&B Company' would generate the following quotation number:

JS-A&B-05052016-01

Further along in the quotation process, the workbook will be renamed to contain the quotation number. This can cause issues where the quotation number contains a special character such as a period (for example, it can mess up the file type.)

Is there a way to make Excel ignore any characters (including spaces) in the company name that aren't letters or numbers? For example, making a company named 'A. & B. Company' generate the quotation number:

JS-ABC-05052016-01

Solution

I think I've found the answer for you

Function removeSpecial(sInput As String) As String
    Dim sSpecialChars As String
    Dim i As Long
    sSpecialChars = "\/:*?""<>|$&"
    For i = 1 To Len(sSpecialChars)
        sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), " ")
    Next
    removeSpecial = sInput
End Function

Alt+F11 - Insert - Module - Paste it in.

You can add more special characters into the array if you wish. here: sSpecialChars = "\/:*?""<>|$&"

Then in your cell, type: =SUBSTITUTE(removeSpecial(A1)," ","")

the result of a &b.company is ab.company

Credits to the author, I've just found it.

then your formula should be:

=UPPER(LEFT(SUBSTITUTE(removeSpecial(P4)," ",""),1)&LEFT(SUBSTITUTE(removeSpecial(P5)," ",""),1)&"-"&LEFT(SUBSTITUTE(removeSpecial(D11)," ",""),3)&"-"&LEFT(SUBSTITUTE(removeSpecial(K6)," ",""),2)&MID(SUBSTITUTE(removeSpecial(K6)," ",""),4,2)&MID(SUBSTITUTE(removeSpecial(K6)," ",""),7,4)&"-"&SUBSTITUTE(removeSpecial(D5)," ",""))

Comments

[Raystafarian]

so you need to change what the formula pulls, not just replace the characters from the already pulled data? Might be a job for regex?

[Raystafarian]

This isn't tagged VBA though

[Adam W]

This solution worked perfectly for me. My apologies - I didn't think to include a VBA tag, although my worksheet does heavily use VBA so it wasn't an issue to add a new function. I have amended the question to include the VBA tag and marked as solved!