|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
 |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
You eat, breathe and sleep innovation. Build your mobile intelligence with BlackBerry® experts this July. Register Today! |
|
#1
July 25th, 2004, 09:24 PM
|
|||
|
|||
|
VBA in Excel- Split 1st & last names from same cell
I have a column containing "Last_Name, First_Name" that needs to be split into two columns, "Last_Name" & "First_Name". Last is separated from first by a comma and a space. Of course, names are of varying length so I can't specifty string length. I'm no programmer by any means, but I can open a macro in the VB editor and make changes as recommended without going into seizures. Any thoughts on how I can make this happen? And since I'll likely need to do it in the future, how can I combine the Last and First names from separate cells into a single cell and separate them with a comma and space? If you only have time to answer one question, the first is the most pressing.
Thanks.
|
|
#2
July 27th, 2004, 01:07 AM
|
|||
|
|||
|
Bump...additional info
I tried creating a macro as follows:
Starting with first cell- "F2" to select active cell, "Ctrl+left" arrow to get to beginning of First_Name, "Backspace Backspace" to erase space and comma, "Shift+End" to select First_Name, "Ctrl=X" cut, "Tab" to index to adjoining cell, "Ctrl+V" paste, "Down Arrow", "Left Arrow" to start over at next combined cell. Macro only runs in the cells I recorded it in. Seems it relates to relative values, but again, I don't know enough to proceed. Any suggestions or leads are greatly appreciated.
|
|
#3
July 27th, 2004, 12:05 PM
|
|||
|
|||
|
You're going to need to write a macro that can tell where your comma is in the string.
Code:
Dim firstName As String
Dim lastName As String
Dim n As Integer
Dim rowNum As Integer
Dim colNum As Integer
rowNum = 1
colNum = 1
While Cells(rowNum, colNum).Value <> ""
n = InStr(1, Cells(rowNum, colNum).Value, ",")
lastName = Left(Cells(rowNum, colNum).Value, n - 1)
firstName = Right(Cells(rowNum, colNum).Value, Len(Cells(rowNum, colNum).Value) - n - 1)
Cells(rowNum, colNum + 1).Value = firstName
Cells(rowNum, colNum + 2).Value = lastName
rowNum = rowNum + 1
Wend
This is the basic code that is needed for what you are asking for it to do. Now depending on where you want the first and last names to go and what column is holding the names, will mean that you will need to change the cell references (I'm using 1 because I had my names start in A1 and then go to A2 ect.) To change it from two cells to one, you'll more or less want to do the same thing, but in reverse, take the two cells and concatenate the values: Code:
cells(1, 3).value = cells(1, 2).value & ", " & cells(1, 1).value Once again, this is assuming that you're first and last names are in cells A1 and B1 respectively. I hope this helps and if you should have any further questions, please feel free to ask.
|
|
#4
July 28th, 2004, 01:06 AM
|
|||
|
|||
|
You rock!!! Thanks a bunch. It worked perfect. Only thing that needed to be changed was the first row reference. Fortunately, I got some help from a local programming whiz that spotted my error. Otherwise, perfect. Thanks for the reply. I'll be visiting again with more questions as they arise, and hopefully maybe offer a little help as I learn more. Great forum too.
|
|
| Viewing: ASP Free Forums > Programming > Visual Basic Programming > VBA in Excel- Split 1st & last names from same cell |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
