Delete Prefix in each cell

Pete2020

Board Regular
Joined
Apr 25, 2020
Messages
68
Office Version
2016
Platform
Windows
Is there any VBA Or Custom Function or Formula which help in removing the prefix numbers and alphabets at the starting of each sentence.


Book1
AB
1RAW DataExpected Output
21. TAS Balagopal – Export management – HimalayaTAS Balagopal – Export management – Himalaya
32. Varshney & Battacharya – International Marketing Sultanchand & sonsVarshney & Battacharya – International Marketing Sultanchand & sons
43. Francis chernmilam – International Trade and Export management – HimalayaFrancis chernmilam – International Trade and Export management – Himalaya
54. B.S Rathor – Export Management – Himalaya.B.S Rathor – Export Management – Himalaya.
65. S. Shivaramu – Export Marketing – HimalayaS. Shivaramu – Export Marketing – Himalaya
7Unit – I: Concept of SecurityUnit – I: Concept of Security
8a) Meaning, definition and ObjectivesMeaning, definition and Objectives
9b) Elements of National SecurityElements of National Security
10c) Kinds of National SecurityKinds of National Security
11REFERENCESREFERENCES
121) Barry Buzon., People, State and FearBarry Buzon., People, State and Fear
132) Bajpai, U.S.,(ed) India’s Security Bajpai, U.S.,(ed) India’s Security
143) Dixit, J.N., Accross BordersDixit, J.N., Accross Borders
154) Satish Kumar, (ed)Satish Kumar, (ed)
165) Jayaramu, P.S., India’s National Security and Foreign PolicyJayaramu, P.S., India’s National Security and Foreign Policy
176) Kaul, T.N., India and the New World Kaul, T.N., India and the New World
187) Kux, Dennis, Estranged DemocraciesKux, Dennis, Estranged Democracies
191.1 NMR SpectroscopyNMR Spectroscopy
202.1 Interpretation of NMR spectra Interpretation of NMR spectra
213.1 Mass spectroscopy Mass spectroscopy
224.1 Interpretation of mass spectra Interpretation of mass spectra
235.1 E.S.R.Spectroscopy E.S.R.Spectroscopy
24a. Meaning, definition and ObjectivesMeaning, definition and Objectives
25b. Elements of National SecurityElements of National Security
26c. Kinds of National SecurityKinds of National Security
27Unit – IV: The State AdministrationUnit – IV: The State Administration
28(a) The Governor- role and powers of Governor.The Governor- role and powers of Governor.
29(b) The role and function of the Chief Minister.The role and function of the Chief Minister.
30(c) The State legislature – its role & functions.The State legislature – its role & functions.
31Unit – IV: The Central AdministrationUnit – V: The Central Administration
32(1.) The President- role and powers of PresidentThe President- role and powers of President
33(2.) The role and function of the MayorThe role and function of the Mayor
34(3.) The State legislature – its role & functions.The State legislature – its role & functions.
35Unit – VI: The Central AdministrationUnit – VI: The Central Administration
36(1). The President- role and powers of PresidentThe President- role and powers of President
37(2). The role and function of the MayorThe role and function of the Mayor
38(3). The State legislature – its role & functions.The State legislature – its role & functions.
39Unit – VII: Concept of SecurityUnit – VII: Concept of Security
40(a). Meaning, definition and ObjectivesMeaning, definition and Objectives
41(b). Elements of National SecurityElements of National Security
42(c). Kinds of National SecurityKinds of National Security
Sheet1


EDIT:
Please remove this pattern as well after applying the macro

(i) Basic Accounting Equation.
(ii) Basic Accounting Liability.
(iii) Basic concept of IFRS.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Tricepsratops

New Member
Joined
Jan 3, 2020
Messages
45
Office Version
365
Platform
Windows
Hello. I see that all your prefixes end with a SPACE. So I came up with a formula that trims the text up to the first space.
The problem is that you dont want to cut the "Unit-#" ones, so I did a special situation for them.
the iferror avoid trouble with rows with no space like row 11.
Code:
Spanish:
=SI.ERROR(SI(ESNUMERO(HALLAR("Unit*";A2))=VERDADERO;A2;DERECHA(A2;LARGO(A2)-HALLAR(" ";A2)));A2)

English: (translation may not be accurate)
=IFERROR(IF(ISNUMBER(SEARCH("Unit*",A2))=TRUE,A2,RIGHT(A2,LEN(A2)-SEARCH(" ",A2))),A2)
I'm sure this isn't the best code but it works for me. Once you get the result, you can select the cells and copy paste as text and you will get cells with only your desired text.
 

Dave87

Board Regular
Joined
Apr 22, 2020
Messages
69
Office Version
365, 2016
Platform
Windows
Check if row 27 is ok, the first space is after "Unit"
 

Watch MrExcel Video

Forum statistics

Threads
1,099,913
Messages
5,471,471
Members
406,764
Latest member
ExcelMaker007

This Week's Hot Topics

Top