1. Forum
  2. Usenet
  3. MICROSOFT.PUBLIC.EXCEL

  • Right(Trim(Mid()))

    From noodnutt@gmail.com@21:1/5 to All on Tue Feb 18 18:25:07 2020
    Hi Team

    Can I Pick your collective brains' a little further please.

    Now that I have my Ship/PO No Concatenation, compliments of Claus, I need to trim some of the fat from them.

    How would I construct the Right(Trim(Mid())) to make this:

    2500720936, 2500720945, 2500720938, 2500720956, 2500720937

    Look Like this

    2500720936, 20945, 20938, 20956, 20937

    I need to trim these as the DB Field size this data will eventually end up in is limited to 75 chars and in-as-much-as there is less than that in this example, there will be time when it will be exceeded.

    Heaps of thanks
    Mark.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Claus Busch@21:1/5 to All on Wed Feb 19 10:44:59 2020
    Hi Mark,

    Am Tue, 18 Feb 2020 18:25:07 -0800 (PST) schrieb noodnutt@gmail.com:

    How would I construct the Right(Trim(Mid())) to make this:

    2500720936, 2500720945, 2500720938, 2500720956, 2500720937

    Look Like this

    2500720936, 20945, 20938, 20956, 20937

    do this into the code to transpose the data:

    Sub Con()
    Dim sWs As Worksheet, tWs As Worksheet
    Dim LRowI As Long, LRowC As Long
    Dim fMatch As Long
    Dim myCnt As Integer, i As Integer
    Dim varPO As Variant
    Dim rngC As Range
    Dim strTemp As String

    Set sWs = Worksheets("Import"): Set tWs = Worksheets("Conv")
    LRowI = sWs.Cells(Rows.Count, "A").End(xlUp).Row

    sWs.Range("Q1:Q" & LRowI).AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=tWs.Range("A1"), Unique:=True
    LRowC = tWs.Cells(Rows.Count, "A").End(xlUp).Row

    For Each rngC In tWs.Range("A2:A" & LRowC)
    fMatch = Application.Match(rngC, sWs.Range("Q1:Q" & LRowI), 0)
    myCnt = Application.CountIf(sWs.Range("Q:Q"), rngC)
    If myCnt > 1 Then
    varPO = Application.Transpose(sWs.Cells(fMatch, "A").Resize(myCnt))
    strTemp = varPO(1)
    For i = 2 To UBound(varPO)
    strTemp = strTemp & ", " & Right(varPO(i), 5)
    Next
    rngC.Offset(, 1) = strTemp
    Else
    rngC.Offset(, 1) = sWs.Cells(fMatch, "A")
    End If
    Next
    End Sub


    Regards
    Claus B.
    --
    Windows10
    Office 2016

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)