Text to Column

[hardeep.kanwar]

Hello

I have Data Mentioned Below

Excel Workbook
	B	C
3	Names	Expected Result
4	Abhay Agarwal Pg-Bus 2010	Abhay Agarwal
5	Archit Vardharajan Pg Bus 2010	Archit Vardharanjan
6	Awninder Pg-Bus 2010	Awninder
7	Ayush Agrawal Pg Bus 2010	Ayush Agrawal
8	BHARVI JOSHI PG BUS 2010	*
9	Faizan Lokhandwala Pg Bus 2010	*
10	Hardik Upadhyay *PG-BUS, 2010	*
11	HEMANG KUMAR BORICHA PG-BUS. 2010	*
12	JAYESH SUTHAR PG-BUS. 2010	*
13	Jobin Thomas Pg Bus 2010	*
14	PERSIS ANTHONY PG-Media. 2010	*
15	Riddhima Kotwani PG-Bus 2010	*
16	SUKHDEV SINGH CHANNE PGDBM 2010	*
17	Sumeet Anand Pg Bus 2010	*
18	Vidhi Mundra PGDBE June 2010	*
19	Akhil Giri PG Media 2010 Pune Campus	*
20	Divyesh Chauhan Pdbm 2010	*
21	Kaushik Pg-Bus 2010	*
22	Kushan Mistry Pd-Bus 2010	*
23	Sajan Shrimali Pg-Bus 2010	*
24	Divya Shobhawat- Pt Media - 2010	*
Ahmd

Some have Like this

Excel Workbook
	B
4	Jayant PDBM 2010
5	Ajay PGDBE 2010
6	ASHUTOSH VYAS 2010
7	MANJEET SINGH PDBM 2010
8	Neha Arora Ug Course 2010
9	PARVEEN KUMAR PDBM 2010
10	RAJAT GUPTA PDBM 2010
11	RAJESH KUMAR SHARMA PDBM 2010
12	Ranbir Singh PDBM 2010
13	Ritika Vaid Pdbm 2010
14	Robin Pdbm 2010
15	Samridhi Sharma Pdbm 2010
Chd

Now , I want to Exact the Only Names from this Data

Any Idea How can i do it?

Thanks in Advance

 

[texasalynn]

Try this

Sub xtract_Name()
Dim s As String
Dim LR As Long
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row 'get last row column A
For i = 2 To LR
    s = LCase(Cells(i, 1))
    If InStrRev(s, "pg") > 0 Then
        Cells(i, 2) = StrConv(Left(s, InStrRev(s, "pg") - 1), vbProperCase)
    End If
    If InStrRev(s, "pd") > 0 Then
        Cells(i, 2) = StrConv(Left(s, InStrRev(s, "pd") - 1), vbProperCase)
    End If
Next i
MsgBox "Done"
End Sub

 

[hardeep.kanwar]

Try this

Sub xtract_Name()
Dim s As String
Dim LR As Long
LR = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row 'get last row column A
For i = 2 To LR
    s = LCase(Cells(i, 1))
    If InStrRev(s, "pg") > 0 Then
        Cells(i, 2) = StrConv(Left(s, InStrRev(s, "pg") - 1), vbProperCase)
    End If
    If InStrRev(s, "pd") > 0 Then
        Cells(i, 2) = StrConv(Left(s, InStrRev(s, "pd") - 1), vbProperCase)
    End If
Next i
MsgBox "Done"
End Sub

Thanks Texasalynn

This Helps me Lot

Thanks Again

 

[cgcamal]

Hi hardeep.kanwar,

An option with formula could be
Copy down the formula in B2:

Excel Workbook
	A	B
1	Names
2	Abhay Agarwal Pg-Bus 2010	Abhay Agarwal
3	Archit Vardharajan Pg Bus 2010	Archit Vardharajan
4	Awninder Pg-Bus 2010	Awninder
5	Ayush Agrawal Pg Bus 2010	Ayush Agrawal
6	BHARVI JOSHI PG BUS 2010	BHARVI JOSHI
7	Faizan Lokhandwala Pg Bus 2010	Faizan Lokhandwala
8	Hardik Upadhyay *PG-BUS, 2010	Hardik Upadhyay
9	HEMANG KUMAR BORICHA PG-BUS. 2010	HEMANG KUMAR BORICHA
10	JAYESH SUTHAR PG-BUS. 2010	JAYESH SUTHAR
11	Jobin Thomas Pg Bus 2010	Jobin Thomas
12	PERSIS ANTHONY PG-Media. 2010	PERSIS ANTHONY
13	Riddhima Kotwani PG-Bus 2010	Riddhima Kotwani
14	SUKHDEV SINGH CHANNE PGDBM 2010	SUKHDEV SINGH CHANNE
15	Sumeet Anand Pg Bus 2010	Sumeet Anand
16	Vidhi Mundra PGDBE June 2010	Vidhi Mundra
17	Akhil Giri PG Media 2010 Pune Campus	Akhil Giri
18	Divyesh Chauhan Pdbm 2010	Divyesh Chauhan
19	Kaushik Pg-Bus 2010	Kaushik
20	Kushan Mistry Pd-Bus 2010	Kushan Mistry
21	Sajan Shrimali Pg-Bus 2010	Sajan Shrimali
22	Divya Shobhawat- Pt Media - 2010	Divya Shobhawat-
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B2	=LEFT(A2,IFERROR(SEARCH(" PD",A2,1),IFERROR(SEARCH(" PG",A2,1),IFERROR(SEARCH(" PT",A2,1),SEARCH(" ~*P",A2,1)))-1))

Hope this helps.

Regards

 

[hardeep.kanwar]

Hi hardeep.kanwar,

An option with formula could be
Copy down the formula in B2:

Excel Workbook
	A	B
1	Names
2	Abhay Agarwal Pg-Bus 2010	Abhay Agarwal
3	Archit Vardharajan Pg Bus 2010	Archit Vardharajan
4	Awninder Pg-Bus 2010	Awninder
5	Ayush Agrawal Pg Bus 2010	Ayush Agrawal
6	BHARVI JOSHI PG BUS 2010	BHARVI JOSHI
7	Faizan Lokhandwala Pg Bus 2010	Faizan Lokhandwala
8	Hardik Upadhyay *PG-BUS, 2010	Hardik Upadhyay
9	HEMANG KUMAR BORICHA PG-BUS. 2010	HEMANG KUMAR BORICHA
10	JAYESH SUTHAR PG-BUS. 2010	JAYESH SUTHAR
11	Jobin Thomas Pg Bus 2010	Jobin Thomas
12	PERSIS ANTHONY PG-Media. 2010	PERSIS ANTHONY
13	Riddhima Kotwani PG-Bus 2010	Riddhima Kotwani
14	SUKHDEV SINGH CHANNE PGDBM 2010	SUKHDEV SINGH CHANNE
15	Sumeet Anand Pg Bus 2010	Sumeet Anand
16	Vidhi Mundra PGDBE June 2010	Vidhi Mundra
17	Akhil Giri PG Media 2010 Pune Campus	Akhil Giri
18	Divyesh Chauhan Pdbm 2010	Divyesh Chauhan
19	Kaushik Pg-Bus 2010	Kaushik
20	Kushan Mistry Pd-Bus 2010	Kushan Mistry
21	Sajan Shrimali Pg-Bus 2010	Sajan Shrimali
22	Divya Shobhawat- Pt Media - 2010	Divya Shobhawat-
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B2	=LEFT(A2,IFERROR(SEARCH(" PD",A2,1),IFERROR(SEARCH(" PG",A2,1),IFERROR(SEARCH(" PT",A2,1),SEARCH(" ~*P",A2,1)))-1))

Hope this helps.

Regards

Hummmmm


That Was Great