Opposite of CONCATENATE
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.
|
Opposite of CONCATENATE
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 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. . |
Opposite of CONCATENATE
You could use the "Left","Right", or "Mid" functions to do
this. -----Original Message----- 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. . |
Opposite of CONCATENATE
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 DataText to Columns Select Delimited, click Next Select the appropriate delimiter (e.g. Space or comma) Click Finish TikoTiko wrote: 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. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Opposite of CONCATENATE
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 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. . |
Opposite of CONCATENATE
(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" wrote in message ... 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 DataText to Columns Select Delimited, click Next Select the appropriate delimiter (e.g. Space or comma) Click Finish TikoTiko wrote: 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. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Opposite of CONCATENATE
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" wrote in message ... 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? |
All times are GMT +1. The time now is 04:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com