Remove Numbers at Beginning of text from a cell

[Pete2020]

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
	A	B
1	Course Intro - Before Macro- Sample data	After Macro
2	1. Introduction	Introduction
3	2.) Get to know things	Get to know things
4	(1). Happy to see You	Happy to see You
5	1. Nice to interact with You	Nice to interact with You
6	Session 1 Number functions	Number functions
7	Session 1 (a) String data types and functions	String data types and functions
8	Session 1B The DUAL table and Number types	The DUAL table and Number types
9	Session 2 Adding a second table	Adding a second table
10	Session 2A Adding a third table	Adding a third table
11	Session 2B Creating and querying part of a table	Creating and querying part of a table
12	Session 2 Practice Activities	Practice Activities
13	Session 2 Summarising and ordering data	Summarising and ordering data
14	Session 3 Database terminology	Like Above Format
15	Session 3a Find missing data, and delete and update data	Like Above Format
16	Session4 Practice Activities	Like Above Format
17	Session5: Practice Activities	Like Above Format
18	Chapter1 Introduction	Like Above Format
19	Chapter 2:Benefits	Like Above Format
20	Section 1 Overview	Like Above Format
21	Section 2: Setting up Project	Like Above Format
22	Part 1 Objectives	Like Above Format
23	Part 2: Outcomes	Like Above Format
24	Step 01 Understanding Full Stack Application Architecture	Like Above Format
25	Step 02 Developing Spring Applications before Spring Boot	Like Above Format
26	Step 02 Quick Overview of Modern JavaScript and TypeScript	Like Above Format
27	Step 02 World before JPA JDBC, Spring JDBC and myBatis	Like Above Format
28	Step 03 Installing Angular CLI Awesome Tool to create Angular Projects	Like Above Format
29	Step 03 - Introduction to JPA	Like Above Format
30	Step 03 Using Spring Initializr to create a Spring Boot Application	Like Above Format
31	Step 04 Creating a JPA Project using Spring Initializr	Like Above Format
32	Step-04 Creating a Simple REST Controller	Like Above Format
33	Step 04: Creating a JPA Project using Spring Initializr	Like Above Format
34	Step 04: Creating a Simple REST Controller	Like Above Format
35	Step-05 Creating a JPA Project using Spring Initializr	Like Above Format
36	Step-05 Creating a Simple REST Controller	Like Above Format
37	Step 1 Introduction to JPA	Like Above Format
38	Step 2 Benefits of SAAS	Like Above Format
39	Exercise 163 Exceptions	Like Above Format
40	Exercise 163 Solution	Like Above Format
41	Exercise 03a Answer	Like Above Format
42	Exercise 03a Question	Like Above Format
43	Exercise 04a Answer	Like Above Format
44	Exercise 04a Question	Like Above Format
45	Exercise 07a Answer	Like Above Format
46	Exercise 07a Question	Like Above Format
47	Day 1 Overview of the Speaker	Like Above Format
48	Day 2.) Topic Overview	Like Above Format
49	Week -1 About the Speaker	Like Above Format
50	Week 2 - Introduction	About the Speaker
51		Introduction
Sheet1

 

[mse330]

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

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

 

[Pete2020]

Thank You for Your Effort. I am happy with your try

 

[mse330]

Glad I could help & thanks for the feedback

 

[Pete2020]

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

 

[mse330]

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 ?

 

[mikerickson]

The error in post#5 is likely a case where b(x) = "" and the upper bound of Split("", " ")=-1. The fix is to use.

If Len(Split(b(x)&" ", " ")(0)) = 1 Then

 

[mse330]

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

 

[Pete2020]

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

 

[mikerickson]

I think this will clean the strings as desired

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