Announcement

Collapse
No announcement yet.

How to determine first two numbers in number for a range of data

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • How to determine first two numbers in number for a range of data



    Hi guys, I'd like to have a code which will determine first two numbers and assign them some value. For instance I will have number 123456 so the function will pick first two numbers (12) and assign them value "KLPOOP", for number 985632 (first two numbers are 98) it will assign "LOOOP" and so on.. I managed to do it for one number with Mid function however it's not working for range of data (dozens of 6 digit numbers). Any suggestions please?

  • #2


    Welcome to the forum!

    Normally, I would use an array for such.

    Replace a,b,c,d, etc. with your variable names.
    Code:
    Sub Main()
      Dim L As Long, s$, i As Integer
      Dim a As Integer, b As Integer, c As Integer, d As Integer
      
      L = 1234567  '[A1]
      s = CStr(L)
      i = WorksheetFunction.RoundDown(Len(s) / 2, 0)
      
      If i * 2 >= 2 Then a = Left(s, 2)
      If i * 2 >= 4 Then b = Mid(s, 3, 2)
      If i * 2 >= 6 Then c = Mid(s, 5, 2)
      If i * 2 >= 8 Then d = Mid(s, 7, 2)
      
      Debug.Print a, b, c, d
    End Sub

    Comment

    Working...
    X