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

 

[Sektor]

Function Abbrev(Str As String, Number As String) As String

    Application.Volatile
    
    Dim i As Integer
    Dim char As String, s As String
    
    ' Get 4 chars.
    For i = 1 To Len(Str)

        char = Mid(Str, i, 1)

        If (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
    
    Abbrev = s & Number

End Function

 

[Taniquetil]

Sorry 1 more, numbers are allowed in the first 4 letters

D2 Printing = D2PR200

This is fine. thanks for your help. An Excel dummy

 

[Taniquetil]

wow thanks, that was fast, i wish i had the slightest inkling to how you achieve this!

 

[Sektor]

Here's adjusted code, if second character is digit.

Function Abbrev(Str As String, Number As String) As String

    Application.Volatile
    
    Dim i As Integer
    Dim s As String, char As String

    For i = 1 To Len(Str)

        char = Mid(Str, i, 1)

        If Len(s) = 1 And IsNumeric(char) Then
            s = s & UCase(char)
        ElseIf (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
    
    Abbrev = s & Number

End Function

 

[Taniquetil]

will this second code work if there is a number anywhre in the first 4 letters?

and im also having trouble pasting this in to VBA, what do i need to do to make a new sheet and get this code to work without having to run all my other codes?

 

[Sektor]

Sorry, here's adjustion.
This one takes only digits and letters.

Function Abbrev(Str As String, Number As String) As String

    Application.Volatile
    
    Dim i As Integer
    Dim s As String, char As String
    

    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
    
    Abbrev = s & Number

End Function

 

[Peter_SSs]

Sorry, here's adjustion.
This one takes only digits and letters.

Function Abbrev(Str As String, Number As String) As String

    Application.Volatile
    
    Dim i As Integer
    Dim s As String, char As String
    

    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
    
    Abbrev = s & Number

End Function

Doesn't this function require the user to work out and input the number to follow the letter code for each line of data?

If so, doesn't the user have to have worked out the letters themselves anyway, so they might just as well enter the whole code manually and not use a function at all?

 

[Sektor]

Peter_SSs,
The thing is Taniquetil didn't say the logic to form digits. That's why I created second argument to pass the numbers he wants.

 

[Peter_SSs]

Peter_SSs,
The thing is Taniquetil didn't say the logic to form digits. That's why I created second argument to pass the numbers he wants.

I thought the first post describes the logic. The first time the letter code occurs the code is 200. If the same letter code repeats the number code is 201 etc.