HELP!!!!!!Urgent

daleyman

Board Regular
Joined
Mar 28, 2002
Messages
167
ok, lets say your list starts in cell A2 and goes down, I will give you three formula for cells B2,C2, & D2, that you need to enter then fill down. I am assuming that the format is always the same, as in your list, and always has all components shown, including the terminating full-stop.

cell B2...
=LEFT(A2,FIND(",",A2)-1)

cell C2...
=MID(A2,FIND(",",A2)+2,LEN(A2)-(FIND(",",A2)+4))

cell D2...
=LEFT(RIGHT(A2,2),1)
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
PS: as will anything like this, i recommend you copy the resulting lists to VALUES ONLY, to get rid of the equations once the job is done.
 
Upvote 0
(hey this topic clean disappeared! I am adding new reply to it in order to try to get it to reappear)
 
Upvote 0
On 2002-04-14 11:04, sugar1525 wrote:
I have a column in EXCEL spreadsheet where the lastname, firstname n the middle initial are in the same column. Something like this..
Abdul, Wes A.
Able, Ursula B.
Albert, Tuome C.
Alexi, Toni D.
Al-Sabah, Tommie E.
Alstain, Tom F.
Aruda, Theo G.

Now I have to separate these first, last and middle initials using functions in 3 seperate columns. I dont want the comma and period when these are displayed in separate columns. I know I can do it by going to Data and then Text to column. But I want to do it by using functions like left, right and mid. Can Somebody help me...It's urgent

Thanx

if it's at all likely that some people may not have a middle intial, use.

Name in A1.

B1 =LEFT(A1,FIND(",",A1)-1)
C1 =IF(ISERROR(FIND(" ",RIGHT(A1,FIND(",",A1)-2))),RIGHT(A1,FIND(",",A1)-2),MID(A1,FIND(",",A1)+2,FIND(" ",RIGHT(A1,FIND(",",A1)))))
D1 =IF(ISERROR(MID(A2,FIND(".",A2)-1,1)),"",MID(A2,FIND(".",A2)-1,1))

hope this helps!
 
Upvote 0
I have a column in EXCEL spreadsheet where the lastname, firstname n the middle initial are in the same column. Something like this..
Abdul, Wes A.
Able, Ursula B.
Albert, Tuome C.
Alexi, Toni D.
Al-Sabah, Tommie E.
Alstain, Tom F.
Aruda, Theo G.

Now I have to separate these first, last and middle initials using functions in 3 seperate columns. I dont want the comma and period when these are displayed in separate columns. I know I can do it by going to Data and then Text to column. But I want to do it by using functions like left, right and mid. Can Somebody help me...It's urgent

Thanx
 
Upvote 0
Hi

I was wondering if I can use one function to get the first name for all the values and similarly for lastname and middle initial coz I have around 114 records and it will be tedious to write the function for the 114 values
 
Upvote 0
Once you have written the equation against one row, grab the fill button (the little square on the bottom right of a selected cell) and drag it down to fill the equation through the list. Alternatively, double click the fill button to automatically fill the equations down to the bottom of the list.

The cell references change for you, when you do this.
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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