# 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

## Answers

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

col9 = Mid(CodeD, 2)

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

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)