Excel get value depending on length of source

Question

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

I suggest you take a look at [this answer](http://stackoverflow.com/a/7239408/4185106), that is actually what you want. — moffeltje, Jun 29 '15 at 11:27
There's something wrong with the sample `Input` data, and/or the code. `Mid(value, 7, 4)` this part will return empty string for both 5 and 6 char input. Also `If` and `ElseIf` conditions in `What I have tried:` are the same. There is no action for `elmno=5` defined. — BrakNicku, Jun 29 '15 at 11:32
I'm just curious about what the elmno parameter is for- Would you mind explaining? — Clon, Jun 29 '15 at 11:52

Rory · Answer 1 · 2015-06-29T11:27:55.620

1

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

col9 = Mid(CodeD, 2)

edited Jun 29 '15 at 11:27

answered Jun 29 '15 at 11:21

Rory

32,730
5
32
35

See updated answer - assuming I understood what GetElm was returning. – Rory Jun 29 '15 at 11:28
No, just replace this part: `col9 = Right(CodeD, 5)` – Rory Jun 29 '15 at 11:31
Nope, that returns both the 4 and 5 digits with the letter on both `L1234` & `L12345` are returned – Matt Jun 29 '15 at 11:33
What exactly does `GetElm` return? – Rory Jun 29 '15 at 11:35
Dont worry, got it working, see my answer, thanks anyway – Matt Jun 29 '15 at 11:36
@moffeltje Yes, I know but since it bears **no relation to any of the sample data**, I can't tell what value it returns, hence my question. – Rory Jun 29 '15 at 11:39
@moffeltje Yup. :) If it returns L12345 as I assumed, then `Mid` would be a simple solution, but it appears not. – Rory Jun 29 '15 at 11:43

Clon · Answer 2 · 2015-06-29T11:44:55.983

1

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

edited Jun 29 '15 at 11:44

answered Jun 29 '15 at 11:39

Clon

1,035
8
12

score 0 · Accepted Answer · answered Jun 29 '15 at 11:36

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)

Excel get value depending on length of source

3 Answers3