Opposite of CONCATENATE

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What command would allow me to do the opposite of CONCATENATE in Excel? I'm trying to break-up a field with a full name, hence i need to extract the first name, middle and last name.
 
the easiest way is to use the Mid, Left and Right
functions.
The instr function can help you find where comma and/or
space separators between the names are.
-----Original Message-----
What command would allow me to do the opposite of
Click to expand...
CONCATENATE in Excel? I'm trying to break-up a field with
a full name, hence i need to extract the first name,
middle and last name.
 
You could use the "Left","Right", or "Mid" functions to do
this.

-----Original Message-----
What command would allow me to do the opposite of
Click to expand...
CONCATENATE in Excel? I'm trying to break-up a field with
a full name, hence i need to extract the first name,
middle and last name.
 
You can use the Text to Columns command to separate the data.

Ensure that there are several blank columns to the right of the
column that contains the names.
Select the cells with names, and choose Data>Text to Columns
Select Delimited, click Next
Select the appropriate delimiter (e.g. Space or comma)
Click Finish
 
The exact problem i'm having is with separating the middle name or middle initial. I can separate a field with a 1st name and last name but i can make this work whenever it includes a middle name. Can u include an example of how to solve this problem?

----- Rone wrote: -----

You could use the "Left","Right", or "Mid" functions to do
this.

-----Original Message-----
What command would allow me to do the opposite of
Click to expand...
CONCATENATE in Excel? I'm trying to break-up a field with
a full name, hence i need to extract the first name,
middle and last name.
 
(Tiko)**2,

If you do not prefer Debora's very practical example, and want to do
something in VBA instead, try:
(This assumes that all names are delimited sith spaces. If you have initials
and periods, you will have to do a little more processing)

Sub splitter()

Dim namefield As String
Dim NameX() As String

namefield = "Alfred Jim Muggs"
NameX = Split(namefield, " ")
For i = LBound(NameX) To UBound(NameX)
msg = msg & "Name " & i + 1 & " is: [" & NameX(i) & "]" &
vbCrLf
Next i
MsgBox msg

End Sub

Alex J

Debra Dalgleish said:
You can use the Text to Columns command to separate the data.

Ensure that there are several blank columns to the right of the
column that contains the names.
Select the cells with names, and choose Data>Text to Columns
Select Delimited, click Next
Select the appropriate delimiter (e.g. Space or comma)
Click Finish
Click to expand...
I'm trying to break-up a field with a full name, hence i need to extract the
first name, middle and last name.
 
See also Chip's page
http://www.cpearson.com/excel/FirstLast.htm

But at the end, no computer can tell for sure whether the "Bølla" in "Arnstein Bølla
Cheng" is a part of the first or the last name, or even whether the family name comes last
or first. There's manual auditing to be done.

--
HTH. Best wishes Harald
Followup to newsgroup only please.

TikoTiko said:
The exact problem i'm having is with separating the middle name or middle initial. I can
Click to expand...
separate a field with a 1st name and last name but i can make this work whenever it
includes a middle name. Can u include an example of how to solve this problem?
 
Back
Top