Abbreviation and numbering question

[Taniquetil]

Hi,


I have a list of account names which i need to give codes to. each code format = First four letter of company name followed by 3 numbers starting at 200

eg. Artshop, Bookstore, Apple Co would be

ARTS200
BOOK200
APPL200

if i were to add artstore, and bookshop and appleseed drinks they would be

ARTS201
BOOK201
APPL201

etc etc. is there a way this can be done if i have a column with the company names in to automatically create these codes?

slight problem. cant have symbols or spaces etc in the code. so they would need to read as follows

Dr & Lyle = DRLY200
St-German = STGE200
St George = STGE201

any help appreciated, i also understand that the latter may be close to impossible so going through manually wouldnt be a problem. maybe making those codes return an error rather than populating the cell with an incorrect value would be better.

Thank you in advance

 

[Taniquetil]

In 300,000 entries i have not come across this, as we add information such as LTD or & Co etc.

eg A1 - we have as A1 LTD - ACC CODE: A1LT200

i am now going to try Micks code! looks promising!

 

[Peter_SSs]

I guess, there will be 3 characters.

Could be, but maybe the requirement is to always finish with 7 characters so some character padding could be required.

 

[Taniquetil]

still getting the error. i do have 4 VBA codes already in this sheet, and am not really sure how to separate them so they dont interefere with each other...this could be the problem?

With regards to the padding required if there is a company with less than 4 letters.

My boss has said we would Pad with Zeros

For a company just called "IT" would be IT00200

although this has never happened your insight and willingness to protect against this eventuality is fantastic!

 

[MickG]

I should first try running it from a commandbutton on a new sheet with my basic data in column "K".
Mick

 

[Taniquetil]

Runtime Error 429, MickG is there any way we could chat on a live basis? msn messenger or so to resolve this as you clearly have it working so the problem is at my end. I have tested on an independent worksheet and the problem still remains

 

[Sektor]

Try this. Range with formulas mustn't have gaps.

Function Abbrev(Str As String) As String

    Application.Volatile
    
    Dim sh As Worksheet
    Dim i As Integer, iNum As Integer
    Dim s As String, char As String
    Dim rng As Range, rngPrev As Range, iOccur As Integer
    
    iNum = 200
    Set rng = Application.ThisCell
    Set sh = rng.Parent

    ' Get 4 characters.
    For i = 1 To Len(Str)
        char = Mid(Str, i, 1)
        If (IsNumeric(char)) Or (Asc(char) >= 65 And Asc(char) <= 90) Or (Asc(char) >= 97 And Asc(char) <= 122) Then
            s = s & UCase(char)
        End If
        
        If Len(s) = 4 Then Exit For
        
    Next
    
    ' Get previous range.
    If rng.Row = 1 Then
        Abbrev = s & 200
        Exit Function
    Else
        With sh
            Set rngPrev = .Range(.Cells(rng.End(xlUp).Row, rng.Column), rng.Offset(-1, 0))
        End With
    End If
    
    ' Get number.
    For i = 1 To rngPrev.Count
        If rngPrev(i) Like s & "*" Then iOccur = iOccur + 1
    Next
    
    Abbrev = s & (200 + iOccur)

End Function

 

[Taniquetil]

Ladies and Gentlemen! we have a winner! perfect my friend thank you for all your help.

Sorry if it was abit confusing. thank you aswell Mick and Peter!

 

[MickG]

Have a look at this example worksheet.
Number Text.xls
Regards Mick