Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extract number.... (2000 sr 1)

    i have into column A a number of ac######.
    My problem is:
    to extrat into comun B the first 10 character
    to extrat into comun C the first 10 character /numer (if existis)
    ...
    ecc...

    with formula or macro is the same.

    In effect is a sequence delimited from slah...
    Attached is the final result....

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract number.... (2000 sr 1)

    Sub ExtractNumbers()
    Dim lngRow As Long
    Dim lngMaxRow As Long
    Dim lngCol As Long
    Dim lngMaxCol As Long
    Dim arrSplit
    Dim strVal As String

    ' last row
    lngMaxRow = Cells(65536, 1).End(xlUp).Row

    ' get rid of " e "
    Range("A1:A" & lngMaxRow).Replace What:=" e ", Replacement:="/", LookAt:=xlPart

    ' loop
    For lngRow = 1 To lngMaxRow
    ' split value
    arrSplit = Split(Cells(lngRow, 1), "/")
    ' first value
    strVal = arrSplit(0)
    Cells(lngRow, 2) = "'" & strVal
    ' number of values-1
    lngMaxCol = UBound(arrSplit)
    If lngMaxCol > 0 Then
    For lngCol = 1 To lngMaxCol
    strVal = Left(strVal, Len(strVal) - Len(arrSplit(lngCol))) & _
    arrSplit(lngCol)
    Cells(lngRow, lngCol + 2) = "'" & strVal
    Next lngCol
    End If
    Next lngRow
    End Sub

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract number.... (2000 sr 1)

    Here is a slightly improved version (takes care of superflluous "/" characters, and doesn't modify the original data):

    Sub ExtractNumbers()
    Dim lngRow As Long
    Dim lngMaxRow As Long
    Dim lngCol As Long
    Dim lngMaxCol As Long
    Dim arrSplit
    Dim strVal As String

    ' last row
    lngMaxRow = Cells(65536, 1).End(xlUp).Row

    ' loop
    For lngRow = 1 To lngMaxRow
    ' replace " e " in value with ""/"
    strVal = Replace(Cells(lngRow, 1), " e ", "/")
    ' split value on "/"
    arrSplit = Split(strVal, "/")
    ' first value
    strVal = arrSplit(0)
    Cells(lngRow, 2) = "'" & strVal
    ' number of values-1
    lngMaxCol = UBound(arrSplit)
    If lngMaxCol > 0 Then
    For lngCol = 1 To lngMaxCol
    If arrSplit(lngCol) <> "" Then
    strVal = Left(strVal, Len(strVal) - _
    Len(arrSplit(lngCol))) & arrSplit(lngCol)
    Cells(lngRow, lngCol + 2) = "'" & strVal
    End If
    Next lngCol
    End If
    Next lngRow
    End Sub

  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract number.... (2000 sr 1)

    Perfect!

  5. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extract number.... (2000 sr 1)

    Hans
    First, allow me to congratulate you on deciphering the requirements. Secondly thank you for the lovely code that I can use to learn from. I haven't quite got it totally figured out yet; but I will.
    Regards
    Don

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract number.... (2000 sr 1)

    Thank you. This was a fun problem to solve.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •