Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: Remove Everything When Numbers End To Adjacent Column

  1. #11
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    331
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove Everything When Numbers End To Adjacent Column

    Quote Originally Posted by Dazzawm View Post
    Sorry does not do anything.
    It works now your data in A1 as the other presented
    then

    Code:
    Sub tester()
     Dim sm As Object, a, lr, i, j, m, sm1, sm2
     lr = Cells(Rows.Count, 1).End(xlUp).Row
     a = Application.Transpose(Cells(1, 1).Resize(lr))
     ReDim b(1 To lr, 1 To 2)
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = "(.+?\d+)|(.?|w)+"
            For j = 1 To lr
            Set m = .Execute(a(j))
            
                Set sm1 = m(0)
                Set sm2 = m(1)
                    b(j, 1) = sm1
                    b(j, 2) = sm2
           
            Next
        End With
        [b1].Resize(UBound(b, 1), 2) = b
    End Sub
    The result in B1:C1.....B6:C6

    Check
    Last edited by mohadin; Oct 7th, 2019 at 03:08 PM.

  2. #12
    Board Regular Dazzawm's Avatar
    Join Date
    Jan 2011
    Location
    UK
    Posts
    3,375
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove Everything When Numbers End To Adjacent Column

    Actually I need it for columns AE and AF please.
    The more I learn the more I realise I knew nothing!

    Thanks For All Your Help

    Home - Windows 10, Excel 2013

    Work - Windows 7, Excel 2010 Home & Business

  3. #13
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    331
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove Everything When Numbers End To Adjacent Column

    Thanks
    change
    Code:
    [a1].Resize(UBound(b, 1), 2) = b
    to
    Code:
    [ae15998].Resize(UBound(b, 1), 2) = b
    Last edited by mohadin; Oct 7th, 2019 at 03:36 PM.

  4. #14
    Board Regular Dazzawm's Avatar
    Join Date
    Jan 2011
    Location
    UK
    Posts
    3,375
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove Everything When Numbers End To Adjacent Column

    This doesn't appear to work either. It removes all my data in column AE and replaces it with numbers like 1.7, 0.4, 1.6 etc..? These happen to be in column A?!
    Last edited by Dazzawm; Oct 8th, 2019 at 03:49 AM.
    The more I learn the more I realise I knew nothing!

    Thanks For All Your Help

    Home - Windows 10, Excel 2013

    Work - Windows 7, Excel 2010 Home & Business

  5. #15
    Board Regular Dazzawm's Avatar
    Join Date
    Jan 2011
    Location
    UK
    Posts
    3,375
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove Everything When Numbers End To Adjacent Column

    Just to recap my data starts in AE2 and would like the ends removed to column AF.
    The more I learn the more I realise I knew nothing!

    Thanks For All Your Help

    Home - Windows 10, Excel 2013

    Work - Windows 7, Excel 2010 Home & Business

  6. #16
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    331
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove Everything When Numbers End To Adjacent Column

    Hi just to make sure I well understand

    Your data are in AE2 down to the end of column AE Ok?
    Then you will need to replace your data with the first part of it (only) and for get the second part?
    or you need the second part to be placed in the next column (AF)?

  7. #17
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,416
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Remove Everything When Numbers End To Adjacent Column

    Quote Originally Posted by Yongle View Post
    Are you over-complicating

    EVERY example provided by you could be returned with

    =LEFT(A2,7)
    @Dazzawm
    What is the answer/comment/alternative sample data in response to this question?
    Last edited by Peter_SSs; Oct 8th, 2019 at 04:26 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #18
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    331
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove Everything When Numbers End To Adjacent Column

    Quote Originally Posted by mohadin View Post
    Hi just to make sure I well understand

    Your data are in AE2 down to the end of column AE Ok?
    Then you will need to replace your data with the first part of it (only) and for get the second part?
    or you need the second part to be placed in the next column (AF)?
    Any way try this demo and let me know how It goes
    Code:
    Sub Demo()
        Dim sm As Object, a, lr, c, i, j, m, sm1, sm2
        Dim x As Range
        Set x = Application.InputBox("Type first range your DATA start from", , , , , , , 8)
        lr = Cells(Rows.Count, x.Column).End(xlUp).Row - 1
        a = Application.Transpose(x.Resize(lr))
        ReDim b(1 To lr, 1 To 2)
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = "(.+?\d+)|(.?|w)+"
            For j = 1 To UBound(a)
                If a(j) <> "" Then
                    Set m = .Execute(a(j))
                    Set sm1 = m(0)
                    Set sm2 = m(1)
                    b(j, 1) = sm1
                    b(j, 2) = sm2
                End If
            Next
        End With
        Set x = Application.InputBox("Where to place the resule", , , , , , , 8)
        c = MsgBox("Do you want the second part to the next column", vbQuestion + vbYesNo + vbDefaultButton2, "Desision")
        If c = vbYes Then
            x = 2
        Else
            x = 1
        End If
        [x].Resize(UBound(b, 1), x) = b
    End Sub

  9. #19
    Board Regular
    Join Date
    Mar 2015
    Location
    Syria
    Posts
    331
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Remove Everything When Numbers End To Adjacent Column

    verII
    Code:
    Sub Demo()
        Dim sm As Object, a, lr, c, i, j, m
        Dim x As Range
        Set x = Application.InputBox("Type first range your DATA start from", , , , , , , 8)
        lr = Cells(Rows.Count, x.Column).End(xlUp).Row - 1
        a = Application.Transpose(x.Resize(lr))
        ReDim b(1 To lr, 1 To 2)
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = "(.+?\d+)|(.?|w)+"
            For j = 1 To UBound(a)
                If a(j) <> "" Then
                    Set m = .Execute(a(j))
                    b(j, 1) = m(0)
                    b(j, 2) = m(1)
                End If
            Next
        End With
        Set x = Application.InputBox("Where to place the resule", , , , , , , 8)
        c = MsgBox("Do you want the second part to the next column", vbQuestion + vbYesNo + vbDefaultButton2, "Desision")
        If c = vbYes Then
            x = 2
        Else
            x = 1
        End If
        [x].Resize(UBound(b, 1), x) = b
    End Sub

  10. #20
    Board Regular
    Join Date
    Mar 2015
    Posts
    4,059
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Remove Everything When Numbers End To Adjacent Column

    @Dazzawm

    Is this the pattern to the first segment of string ?

    ( ONE or more ) ALPHA followed (ONE or more) NUMERIC

    Here is UDF that can be used in the worksheet
    Code:
    Function Get_Item(ByVal Text As String) As String
        With CreateObject("vbscript.regexp")
            .Global = True
            .Pattern = "[a-zA-Z]+[0-9]+"
            If .Test(Text) Then Get_Item = .Execute(Text)(0)
        End With
    End Function
    Or can be called in a macro like this (results in columns F & G below)
    Code:
    Sub CallFunction()
        Dim cel As Range, x As String
        For Each cel In ActiveSheet.Range("A2:A8")
            x = Get_Item(cel)
            cel.Offset(, 5) = x
            cel.Offset(, 6) = Replace(cel, x, "")
        Next cel
    End Sub
    Excel 2016 (Windows) 32 bit
    A
    B
    C
    D
    E
    F
    G
    1
    Oiginal Result1
    (UDF)
    Result2 B2 copied down C2 copied down Result1
    from VBA
    Result2
    from VBA
    2
    WA11711N WA11711 N =Get_Item(A2) =SUBSTITUTE(A2,B2,"") WA11711 N
    3
    WA20293R WA20293 R WA20293 R
    4
    WA22721N-WSD WA22721 N-WSD WA22721 N-WSD
    5
    WA20563-OS WA20563 -OS WA20563 -OS
    6
    WA20700N WA20700 N WA20700 N
    7
    WA20562-OS WA20562 -OS WA20562 -OS
    8
    WA13381N-6G WA13381 N-6G WA13381 N-6G
    Sheet: Sheet4

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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