Page 1 of 3 123 LastLast
Results 1 to 10 of 21

Thread: Stripping text in Excel

  1. #1
    New Member
    Join Date
    Oct 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Stripping text in Excel

    Hi everyone,

    Is there a chance somebody can please help with a formula which will basically remove all the text from this text block (example) pasted in excel:

    In literary theory, a text is any object that can be "read", whether this object is a work of literature, TK8881122 a street sign, an arrangement of buildings on a city block, or styles of clothing.
    It is a coherent set of signs that transmits some kind of informative message.[1]
    This set of signs is considered in terms of the informative message's content, rather than in terms of its physical form or the medium in which it is represented.
    Within the field of literary criticism, "text" also refers to the original information content of a NM12345 particular piece of writing; that is, the "text" of a work is that primal symbolic arrangement of letters as originally composed, apart from later alterations, deterioration, commentary, translations, paratext, etc.

    What I am hopping to get is all the text to be remove leaving just:
    TK8881122
    NM12345

    Thanks a lot



  2. #2
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Stripping text in Excel

    Welcome to the MrExcel board!

    What would be the verbal 'rule' for what you want out of any text?

    Is it "any 2 upper case letters followed by 1 or more digits" or you specify your requirement.

    What is the longest text that might need to be extracted? The longest of the examples is 9 characters.

    Can there be more, or less, than two items to extract from a text?

    Is the bold text you quoted all in a single cell?

    Is a VBA solution acceptable?
    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

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

    Default Re: Stripping text in Excel

    HI
    how about
    Code:
    Sub test() 
    Dim a As Variant
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = "[A-Z]+\d+"
            Set m = .Execute(Cells(1))
            ReDim a(1 To m.Count)
            For i = 0 To m.Count - 1
                a(i + 1) = m(i)
            Next
        End With
        Cells(1, 2).Resize(UBound(a)) = Application.Transpose(a)
    End Sub
    Last edited by mohadin; Oct 10th, 2019 at 03:32 AM.

  4. #4
    New Member
    Join Date
    Oct 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Stripping text in Excel

    Quote Originally Posted by Peter_SSs View Post
    Welcome to the MrExcel board!

    What would be the verbal 'rule' for what you want out of any text?

    Is it "any 2 upper case letters followed by 1 or more digits" or you specify your requirement.

    What is the longest text that might need to be extracted? The longest of the examples is 9 characters.

    Can there be more, or less, than two items to extract from a text?

    Is the bold text you quoted all in a single cell?

    Is a VBA solution acceptable?
    Hi Peter, the verbal rule is "any amount of upper or lower case letters followed by 1 or more digits"
    The longest text is probably no longer than 22 characters.
    Yes it can be more or less than 2 items to extract from a text
    The text is not necessary bold, can be just normal text too.
    The text can be across the cells, but can be all copied in one cell if needed.
    VBA solution might be accessible

    Thank you

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Stripping text in Excel

    Thanks for the clarifications. See if this user-defined function helps. To implement ..
    1. Right click the sheet name tab and choose "View Code".
    2. In the Visual Basic window use the menu to Insert|Module
    3. Copy and Paste the code below into the main right hand pane that opens at step 2.
    4. Close the Visual Basic window.
    5. Enter the formula as shown in the screen shot below and copy across & down.
    6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

    Code:
    Function GetParts(s As String, num As Long) As String
      Static RX As Object, m As Object
      
      If RX Is Nothing Then
        Set RX = CreateObject("VBScript.RegExp")
        RX.Global = True
      End If
      RX.Pattern = "[A-Z]+\d+"
      Set m = RX.Execute(s)
      If num <= m.Count Then GetParts = m.Item(num - 1)
    End Function
    Sheet1

    ABCDE
    1In literary theory, a text is any object that can be "read", whether this object is a work of literature, TK8881122 a street sign, an arrangement of buildings on a city block, or styles of clothing.
    It is a coherent set of signs that transmits some kind of informative message.[1]
    This set of signs is considered in terms of the informative message's content, rather than in terms of its physical form or the medium in which it is represented.
    Within the field of literary criticism, "text" also refers to the original information content of a NM12345 particular piece of writing; that is, the "text" of a work is that primal symbolic arrangement of letters as originally composed, apart from later alterations, deterioration, commentary, translations, paratext, etc.
    TK8881122NM12345
    2Nothing here meets the pattern
    3
    4text HGFTY76509876 more text F0, and more G876567404323.HGFTY76509876F0G876567404323

    Spreadsheet Formulas
    CellFormula
    B1=GetParts($A1,COLUMNS($B1:B1))


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by Peter_SSs; Oct 11th, 2019 at 03:44 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

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

    Default Re: Stripping text in Excel

    Hi again
    What about
    Code:
    Sub test()
        Dim a, m As Variant
        Dim lr, t, i As Long
        lr = Cells(Rows.Count, 1).End(xlUp).Row
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = "[A-Z]+\d+"
            For t = 1 To lr
                If .test(Cells(t, 1)) Then
                    Set m = .Execute(Cells(t, 1))
                    ReDim a(1 To m.Count)
                    For i = 0 To m.Count - 1
                        a(i + 1) = m(i)
                    Next
                    Cells(t, 2).Resize(, UBound(a)) = a
                End If
            Next
        End With
    End Sub

  7. #7
    New Member
    Join Date
    Oct 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Stripping text in Excel

    Thanks a lot for the code Peter, I just wanted to ask if you can please modify the code for "any amount of upper or lower case letters followed by 1 or more digits or symbols like - _ / ; \ # etc"

    Also for each number found and filtered it will be shown in individual cell, however is there a chance all those numbers can be shown in one cell at once with space between them? Only if that is possible. Thank you very much

    Quote Originally Posted by Peter_SSs View Post
    Thanks for the clarifications. See if this user-defined function helps. To implement ..
    1. Right click the sheet name tab and choose "View Code".
    2. In the Visual Basic window use the menu to Insert|Module
    3. Copy and Paste the code below into the main right hand pane that opens at step 2.
    4. Close the Visual Basic window.
    5. Enter the formula as shown in the screen shot below and copy across & down.
    6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

    Code:
    Function GetParts(s As String, num As Long) As String
      Static RX As Object, m As Object
      
      If RX Is Nothing Then
        Set RX = CreateObject("VBScript.RegExp")
        RX.Global = True
      End If
      RX.Pattern = "[A-Z]+\d+"
      Set m = RX.Execute(s)
      If num <= m.Count Then GetParts = m.Item(num - 1)
    End Function
    Sheet1

    A B C D E
    1 In literary theory, a text is any object that can be "read", whether this object is a work of literature, TK8881122 a street sign, an arrangement of buildings on a city block, or styles of clothing.
    It is a coherent set of signs that transmits some kind of informative message.[1]
    This set of signs is considered in terms of the informative message's content, rather than in terms of its physical form or the medium in which it is represented.
    Within the field of literary criticism, "text" also refers to the original information content of a NM12345 particular piece of writing; that is, the "text" of a work is that primal symbolic arrangement of letters as originally composed, apart from later alterations, deterioration, commentary, translations, paratext, etc.
    TK8881122 NM12345
    2 Nothing here meets the pattern
    3
    4 text HGFTY76509876 more text F0, and more G876567404323. HGFTY76509876 F0 G876567404323

    Spreadsheet Formulas
    Cell Formula
    B1 =GetParts($A1,COLUMNS($B1:B1))


    Excel tables to the web >> Excel Jeanie HTML 4

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

    Default Re: Stripping text in Excel

    Hi what about
    Code:
    Sub test()
        Dim a, m As Variant
        Dim lr, t, i As Long
        lr = Cells(Rows.Count, 1).End(xlUp).Row
        With CreateObject("VBScript.RegExp")
            .Global = True
            .Pattern = "[.A-Za-z]+.?\d+."
            For t = 1 To lr
                If .test(Cells(t, 1)) Then
                    Set m = .Execute(Cells(t, 1))
                    ReDim a(1 To m.Count)
                    For i = 0 To m.Count - 1
                        a(i + 1) = m(i)
                    Next
                    Cells(t, 3) = Join(a, " ")
                End If
            Next
        End With
    End Sub

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,247
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Stripping text in Excel

    Quote Originally Posted by nevolex View Post
    I just wanted to ask if you can please modify the code for "[COLOR=#333333]any amount of upper or lower case letters followed by 1 or more digits or symbols like - _ / ; \ # etc"
    I think you will need to tighten up the above rule somewhat or else explain why message.[1] was not outputted from this sentence in your cell A1 example text...

    "It is a coherent set of signs that transmits some kind of informative message.[1]"
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #10
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Stripping text in Excel

    Quote Originally Posted by nevolex View Post
    ... followed by 1 or more digits or symbols like - _ / ; \ # etc"
    First thing for me is to clarify this part.
    a) Is that red "-" part of the list of characters to look for or not?
    b) What exactly does "etc" mean at the end. We don't know what symbols you want to include in your search and what to exclude. You have to tell us.

    A few more short samples with expected results might also help clarify.

    BTW, best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only.
    Last edited by Peter_SSs; Oct 12th, 2019 at 03:00 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

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
  •