Stripping text in Excel

nevolex

New Member
Joined
Oct 10, 2019
Messages
6
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


 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,246
Office Version
365
Platform
Windows
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?
 

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
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:

nevolex

New Member
Joined
Oct 10, 2019
Messages
6
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
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,246
Office Version
365
Platform
Windows
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)

Rich (BB 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
Excel Workbook
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.NM12345
2Nothing here meets the pattern
3
4text HGFTY76509876 more text F0, and more G876567404323.HGFTY76509876F0G876567404323
Sheet1
 
Last edited:

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
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
 

nevolex

New Member
Joined
Oct 10, 2019
Messages
6
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

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)

Rich (BB 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

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:501px;"><col style="width:136px;"><col style="width:87px;"><col style="width:120px;"><col style="width:68px;"></colgroup><tbody>
</tbody>

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

<tbody>
</tbody>

<tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,504
Office Version
2010
Platform
Windows
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"

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]"
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
42,246
Office Version
365
Platform
Windows
... 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:

Forum statistics

Threads
1,082,131
Messages
5,363,337
Members
400,726
Latest member
Shahzad Taimoor

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top