Remove Numbers at Beginning of text from a cell

Pete2020

Board Regular
Joined
Apr 25, 2020
Messages
68
Office Version
  1. 2016
Platform
  1. Windows
I have huge text data contains list of topics and course contents.I am facing a challenge to remove intro section numbers and session Numbers and much more to a normal text pattern. Individual removing them is taking a lot of time and effort.

I need the help of Mr. Excel Experts in resolving this through a Macro or VB Function.In the sample data, I have every thing in column A and my request to remove the intros text and numbers in the same column A.

I observed that in my sample
1. Most of the intros patterns is having serially numbered row after the other
2. 25% of data is followed by intros and text in below row without numbers (Chapter 1 followed by topics with out numbers and Chapter2 so on)

Out Put
=======
I am looking for a solution to remove all numbers and text before the number as shown in the example


Book1
AB
1Course Intro - Before Macro- Sample dataAfter Macro
21. IntroductionIntroduction
32.) Get to know thingsGet to know things
4(1). Happy to see YouHappy to see You
51. Nice to interact with YouNice to interact with You
6Session 1 Number functionsNumber functions
7Session 1 (a) String data types and functionsString data types and functions
8Session 1B The DUAL table and Number typesThe DUAL table and Number types
9Session 2 Adding a second tableAdding a second table
10Session 2A Adding a third tableAdding a third table
11Session 2B Creating and querying part of a tableCreating and querying part of a table
12Session 2 Practice ActivitiesPractice Activities
13Session 2 Summarising and ordering dataSummarising and ordering data
14Session 3 Database terminologyLike Above Format
15Session 3a Find missing data, and delete and update dataLike Above Format
16Session4 Practice ActivitiesLike Above Format
17Session5: Practice ActivitiesLike Above Format
18Chapter1 IntroductionLike Above Format
19Chapter 2:BenefitsLike Above Format
20Section 1 OverviewLike Above Format
21Section 2: Setting up ProjectLike Above Format
22Part 1 ObjectivesLike Above Format
23Part 2: OutcomesLike Above Format
24Step 01 Understanding Full Stack Application ArchitectureLike Above Format
25Step 02 Developing Spring Applications before Spring BootLike Above Format
26Step 02 Quick Overview of Modern JavaScript and TypeScriptLike Above Format
27Step 02 World before JPA JDBC, Spring JDBC and myBatisLike Above Format
28Step 03 Installing Angular CLI Awesome Tool to create Angular ProjectsLike Above Format
29Step 03 - Introduction to JPALike Above Format
30Step 03 Using Spring Initializr to create a Spring Boot ApplicationLike Above Format
31Step 04 Creating a JPA Project using Spring InitializrLike Above Format
32Step-04 Creating a Simple REST ControllerLike Above Format
33Step 04: Creating a JPA Project using Spring InitializrLike Above Format
34Step 04: Creating a Simple REST ControllerLike Above Format
35Step-05 Creating a JPA Project using Spring InitializrLike Above Format
36Step-05 Creating a Simple REST ControllerLike Above Format
37Step 1 Introduction to JPALike Above Format
38Step 2 Benefits of SAASLike Above Format
39Exercise 163 ExceptionsLike Above Format
40Exercise 163 SolutionLike Above Format
41Exercise 03a AnswerLike Above Format
42Exercise 03a QuestionLike Above Format
43Exercise 04a AnswerLike Above Format
44Exercise 04a QuestionLike Above Format
45Exercise 07a AnswerLike Above Format
46Exercise 07a QuestionLike Above Format
47Day 1 Overview of the SpeakerLike Above Format
48Day 2.) Topic OverviewLike Above Format
49Week -1 About the SpeakerLike Above Format
50Week 2 - IntroductionAbout the Speaker
51Introduction
Sheet1
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
While I am sure some other forum member can pull off this job much better with RegEx but give the below code a try & see if it suffice

VBA Code:
Sub a()

Dim a
a = Range("A2", Range("A" & Rows.Count).End(xlUp))

ReDim b(1 To UBound(a))
For x = 1 To UBound(a)
    For i = Len(a(x, 1)) To 1 Step -1
        Select Case Asc(Mid(a(x, 1), i, 1))
            Case 32, 65 To 90, 97 To 122
            Case Else
                b(x) = Trim(Mid(a(x, 1), i + 1))
                If Len(Split(b(x), " ")(0)) = 1 Then b(x) = Trim(Mid(b(x), InStr(b(x), " ")))
                Exit For
        End Select
    Next
Next

[A2].Resize(UBound(b)) = Application.Transpose(b)

End Sub
 
Upvote 0
Glad I could help & thanks for the feedback
 
Upvote 0
I Just tried the macro.For the first time it run well. Its working fine actually.

On subsequent check its showing an error. Can you tell me what is going wrong with the code

spit.JPG
 
Upvote 0
Unfortunately, my code logic will not work . The logic I used to check each line from end to beginning of the phrase & stop whenever a non-alphabetic character appears. In the picture you posted in post #5, I can see "5.7 RDD Transformation part 1" where my code fails as it stops because the last character is 1 and it will show nothing

Is there any chance where you might have numbers in the middle of the cell or is it always at the beginning (unwanted) and at the end such as the example provided ?
 
Upvote 0
The error in post#5 is likely a case where b(x) = "" and the upper bound of Split("", " ")=-1. The fix is to use.
VBA Code:
If Len(Split(b(x)&" ", " ")(0)) = 1 Then
 
Upvote 0
Thanks Mike for the tip. Your suggestion do fix the error but it won't give the expected result for the OP based on my code
 
Upvote 0
Yes my sample has numbers in the middle of the cell.

If Possible Try to provide a scripting library
- where i will insert words that are to be removed if followed by any number
("*part*,"*section*","*chapter*","*session*")
- Code should find any numerics attached before or after by 3 charaters with a single space or without 1
- and remove characters like("-",":","()") from it
- By default it should delete any starting numbers in a cell
 
Upvote 0
I think this will clean the strings as desired
VBA Code:
Function RemoveHeading(aString As String) As String
    Const Punctuation As String = ".,;:?-": Rem note that the - is last.
    Dim Words As Variant
    Dim i As Long, j As Long
   
    For i = 1 To Len(Punctuation)
        aString = Replace(aString, Mid(Punctuation, i, 1), Mid(Punctuation, i, 1) & " ")
    Next i
    aString = WorksheetFunction.Trim(aString)
   
    Words = Split(aString, " ")
   
    Rem find first word with a numeral
    For i = 0 To UBound(Words)
        If Words(i) Like "*#*" Then
            Exit For
        End If
    Next i
   
    If UBound(Words) < i Then
        RemoveHeading = Join(Words, " ")
    ElseIf UBound(Words) = i Then
        RemoveHeading = vbNullString
    Else
        For j = 0 To i
            Words(j) = vbNullString
        Next j
       
        If Words(i + 1) Like "*[)(" & Punctuation & "]" Then
            Words(i + 1) = vbNullString
        End If
        RemoveHeading = Join(Words, " ")
    End If
    RemoveHeading = WorksheetFunction.Trim(RemoveHeading)
End Function
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top