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
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)," ",""))
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments