Split contents of a cell

  • Thread starter Thread starter Kagsy
  • Start date Start date
K

Kagsy

Dear All

I have been given a spreadsheet that has the full name of people in one
cell, e.g. Kagsy Malone. A combination of what functions would enable me to
parse the cell into the two component parts Kagsy & Malone.

In Access I could use:

[Names] = "Kagsy Malone" Kagsy
FirstName: Left([Names],InStr(1,[Names]," ")-1)

or

[Names] = "Kagsy Malone" Malone
LastName: Right(Trim([Names]),Len(Trim([Names]))-InStr(1,[Names]," "))

Thanks for the help...

Kagsy
 
Frank

Thanks for the info,

It was easier for me in Access. Afterall why not have an Instr function in
Excel as well!?

Thanks again.

Kagsy


Frank Kabel said:
Hi
have a look at
http://www.cpearson.com/excel/FirstLast.htm

you can use quite similar functions in Excel:
=LEFT(A1,FIND(" ",A1)-1)
if cell A1 stores your name

or
=TRIM(MID(A1,FIND(" ",A1)+1,255))

--
Regards
Frank Kabel
Frankfurt, Germany
Kagsy said:
Dear All

I have been given a spreadsheet that has the full name of people in one
cell, e.g. Kagsy Malone. A combination of what functions would enable me to
parse the cell into the two component parts Kagsy & Malone.

In Access I could use:

[Names] = "Kagsy Malone" Kagsy
FirstName: Left([Names],InStr(1,[Names]," ")-1)

or

[Names] = "Kagsy Malone" Malone
LastName: Right(Trim([Names]),Len(Trim([Names]))-InStr(1,[Names]," "))

Thanks for the help...

Kagsy
Click to expand...
Click to expand...
 
Hi
FIND/SEARCH do soemthing simila. You have an inStr function in VBA. I
have to look at MS Access what the difference between InStr and FIND
exactly is but for most cases you may use FIND without any problems :-)

--
Regards
Frank Kabel
Frankfurt, Germany
Frank

Thanks for the info,

It was easier for me in Access. Afterall why not have an Instr
function in Excel as well!?

Thanks again.

Kagsy


Frank Kabel said:
Hi
have a look at
http://www.cpearson.com/excel/FirstLast.htm

you can use quite similar functions in Excel:
=LEFT(A1,FIND(" ",A1)-1)
if cell A1 stores your name

or
=TRIM(MID(A1,FIND(" ",A1)+1,255))

--
Regards
Frank Kabel
Frankfurt, Germany
Kagsy said:
Dear All

I have been given a spreadsheet that has the full name of people in
one cell, e.g. Kagsy Malone. A combination of what functions would
enable me to parse the cell into the two component parts Kagsy &
Malone.

In Access I could use:

[Names] = "Kagsy Malone" Kagsy
FirstName: Left([Names],InStr(1,[Names]," ")-1)

or

[Names] = "Kagsy Malone" Malone
LastName: Right(Trim([Names]),Len(Trim([Names]))-InStr(1,[Names],"
"))

Thanks for the help...

Kagsy
Click to expand...
Click to expand...
Click to expand...
 
Back
Top