HELP!!!!!!Urgent

[daleyman]

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)

[daleyman]

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.

[daleyman]

(hey this topic clean disappeared! I am adding new reply to it in order to try to get it to reappear)

[Ian Mac]

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!

[sugar1525]

Thanx so much guys..

It works fine..

[sugar1525]

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

[sugar1525]

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

[daleyman]

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.

[kinkyparamour]

How bout the Data Text to Columns comand?


Denny

[ This Message was edited by: kinkyparamour on 2002-04-15 09:39 ]