Text to Column

hardeep.kanwar

hardeep.kanwar

Well-known Member
Joined
Aug 13, 2008
Messages
691
Hello Experts

I have Name and Course( PG or UG) and year and Month in One Cell

How can i get only Name

Excel Workbook
B
2Aakanksha Singh Parmar
3Aakiff Husain K.
4Aalooran Rahman Bora
5AANCHAL KOCHAR
6Aashima Jain PGDBE 2009
7Aashima Jain PGHR April09
8Abdul Basit
9Abdul Shakeeb
10Abeer Shah
11Abhay Kumar
12Abhijeet Kuchya
13Abhijit Chattaraj
14Abhijit Gaidhane Pgdbe Sept08
15Abhimanyu Bardwaj' 2008
16Abhinav Adi
17Abhinav Teja Jvvs Pg March, 09
18Abhinay Jadeja UG *4 Yrs Aug
19Abhisek Hota
20Abhishek Agarwal Pg Sep, 08
21Abhishek Bafna PG Bus. Oct
22Abhishek Chakraborty Pgft Aug 08
23Abhishek David George *Pgdbe'2008
24Abhishek Dwivedi Pgdbe 08
25Abhishek G
26Abhishek Goswami
27Abhishek Kr. Gupta
28Abhishek Kumar
29Abhishek Pathak Pgdbe
30Abhishek Purohit PGDBE June09
31Abhishek Singh Tomer
32Abhishek Tiwari Pgbe Sept 08
33Abhishek Tyagi Pgdbe
34Abilaasha S. *
35Abinash Mishra
36Abishek Koradiya Pgdbe
37Abishek Kumar Singh
38Abishek Yadav B
39Abu Sufiyan Ansari Pgdbe Aug 08
40Adarsh D Ug Int Aug
41Adeel Ahmed
42Adeel Siddiqi PGMktg June09
43Adi Narayana Gummanuru
44Adil Kazi Pgdbe (HR) July 08
Mine



Expected Result

Excel Workbook
A
1Expected Result
2Aakanksha Singh Parmar
3Aakiff Husain K.
4Aalooran Rahman Bora
5AANCHAL KOCHAR
6Aashima Jain
7Aashima Jain
8Abdul Basit
9Abdul Shakeeb
10Abeer Shah
11Abhay Kumar
12Abhijeet Kuchya
13Abhijit Chattaraj
14Abhijit Gaidhane
15Abhimanyu Bardwaj
16Abhinav Adi
17Abhinav Teja Jvvs
18Abhinay Jadeja
19Abhisek Hota
20Abhishek Agarwal
21Abhishek Bafna
22Abhishek Chakraborty
23Abhishek David George
24Abhishek Dwivedi
25Abhishek G
26Abhishek Goswami
27Abhishek Kr. Gupta
28Abhishek Kumar
29Abhishek Pathak
30Abhishek Purohit
31Abhishek Singh Tomer
32Abhishek Tiwari
33Abhishek Tyagi
34Abilaasha S
35Abinash Mishra
36Abishek Koradiya
37Abishek Kumar Singh
38Abishek Yadav B
39Abu Sufiyan Ansari
40Adarsh D
41Adeel Ahmed
42Adeel Siddiqi
43Adi Narayana Gummanuru
44Adil Kazi
Sheet1



Thanks in Advance
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
One solution is to split cell value and put every string in cells consecutives.
After you cancel string what you don't want and then merge string with union method.
For split string:
Code: 
Sub split_string()
Dim strg As Variant
    Dim ind, r As Integer
nr = Cells(Rows.Count, 1).End(xlUp).Row
r = 1
Do
    strg = Split(Cells(r, 1), " ") 'choose exact column
      For ind = LBound(strg) To UBound(strg)
          Cells(r, ind + 4) = (strg(ind))
      Next
      r = r + 1
Loop Until r = nr
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

hardeep.kanwar
  • Question
Insert Blank Rows
Replies
2
Views
525
hardeep.kanwar
hardeep.kanwar

Forum statistics

Threads
1,217,379
Messages
6,136,216
Members
450,000
Latest member
jgp19

Share this page

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