CODEWITHSUNDEEP
vbaexcel
Matt
Matt

Reputation: 15061

Excel get value depending on length of source

Currently we have a small bit of code that gets the value of a cell and returns 4 digits of it.

For example L1234 would be 1234, D1234 would be 1234

However now we have values that are 5 digits L12345 for example and they are just being returned as the last 2 digits. e.g. L12345 is being returned as 45

What i want is to modify the code to allow for both 4 and 5 digit variants.

Current Code:

  If GetElm(Range("F" & i).value, 3) = "8260" Then
    CodeD = GetElm(Range("F" & i).value, 4)
  End If
  col9 = Right(CodeD, 4)

This returns:

Input   Output
L1234   1234
L12345  45

What I have tried:

  If GetElm(Range("F" & i).value, 3) = "8260" Then
    CodeD = GetElm(Range("F" & i).value, 4)
  ElseIf GetElm(Range("F" & i).value, 3) = "8260" Then
    CodeD = GetElm(Range("F" & i).value, 5)
  End If
  col9 = Right(CodeD, 5)

This Returns:

Input   Output
L1234   L1234
L12345  12345

This returns the 5 digit ones correctly, but the 4 digit ones are being returned with the letter.

EDIT:

GetElm Definition:

Function GetElm(value As String, elmno As Integer)

If elmno = 1 Then
  GetElm = Left(value, 1)
ElseIf elmno = 2 Then
  GetElm = Mid(value, 3, 3)
ElseIf elmno = 3 Then
  GetElm = Mid(value, 7, 4)
ElseIf elmno = 4 Then
  GetElm = Mid(value, 12, 8)
End If

End Function

Upvotes: 1

Views: 56

Answers (3)

Clon
Clon

Reputation: 1055

If all you want to do is to skip the first character in the cell's value, then:

Function GetElm (byval value as string) as string
   GetElm = Right(value, Len(value)-1)
End Function

should do the trick.

This assumes you always have a 1-letter, n-digits code.

Still, I don't understand the use for the second parameter in your GetElm function definition.

Regards, Luis

Upvotes: 1

Matt
Matt

Reputation: 15061

Added function:

Function onlyDigits(s As String) As String
    ' Variables needed (remember to use "option explicit").   '
    Dim retval As String    ' This is the return string.      '
    Dim i As Integer        ' Counter for character position. '

    ' Initialise return string to empty                       '
    retval = ""

    ' For every character in input string, copy digits to     '
    '   return string.                                        '
    For i = 1 To Len(s)
        If Mid(s, i, 1) >= "0" And Mid(s, i, 1) <= "9" Then
            retval = retval + Mid(s, i, 1)
        End If
    Next

    ' Then return the return string.                          '
    onlyDigits = retval
End Function

Then used the code:

  If GetElm(Range("F" & i).value, 3) = "8260" Then
    CodeD = GetElm(Range("F" & i).value, 4)
  End If
  col9 = onlyDigits(CodeD)

Upvotes: 0

Rory
Rory

Reputation: 34075

If you always want to just skip the first character, you can use:

col9 = Mid(CodeD, 2)

Upvotes: 1

Related Questions