Inserting and deleting spaces with a macro
Ok, so I have names in a column which I want to both insert a space afte a "," and then delete everything after the second word space. i.e James,Patrick Coogan needs to be James, Patrick Can this be done? Thanks in advance You guys have been very patient and I very much appreciate all th help. Patrick: -- crowdx4 ----------------------------------------------------------------------- crowdx42's Profile: http://www.excelforum.com/member.php...fo&userid=3774 View this thread: http://www.excelforum.com/showthread.php?threadid=57405 |
Inserting and deleting spaces with a macro
Assuming the only comma and space are as indicated, this UDF should do it.
Public Function FixName(argRange As Range) As String FixName = Replace(Split(argRange, " ")(0), ",", ", ") End Function You should add error checking to ensure that argRange is a single cell. NickHK "crowdx42" wrote in message ... Ok, so I have names in a column which I want to both insert a space after a "," and then delete everything after the second word space. i.e James,Patrick Coogan needs to be James, Patrick Can this be done? Thanks in advance You guys have been very patient and I very much appreciate all the help. Patrick:) -- crowdx42 ------------------------------------------------------------------------ crowdx42's Profile: http://www.excelforum.com/member.php...o&userid=37749 View this thread: http://www.excelforum.com/showthread...hreadid=574054 |
Inserting and deleting spaces with a macro
=LEFT(A1,FIND(",",A1,1)-1) & ", " & MID(A1,FIND(",",A1,1)+1,(FIND(" ",A1,1)-FIND(",",A1,1))) or Public Function CleanUpString(nameInCell As String) As String CleanUpString = Left(nameInCell, InStr(1, nameInCell, ",", vbTextCompare) - 1) & ", " & Mid(nameInCell, InStr(1, nameInCell, ",", vbTextCompare) + 1, (InStr(1, nameInCell, " ", vbTextCompare) - InStr(1, nameInCell, ",", vbTextCompare))) End Function "crowdx42" schreef in bericht ... Ok, so I have names in a column which I want to both insert a space after a "," and then delete everything after the second word space. i.e James,Patrick Coogan needs to be James, Patrick Can this be done? Thanks in advance You guys have been very patient and I very much appreciate all the help. Patrick:) -- crowdx42 ------------------------------------------------------------------------ crowdx42's Profile: http://www.excelforum.com/member.php...o&userid=37749 View this thread: http://www.excelforum.com/showthread...hreadid=574054 |
Inserting and deleting spaces with a macro
"NickHK" schreef in bericht ... Assuming the only comma and space are as indicated, this UDF should do it. Public Function FixName(argRange As Range) As String FixName = Replace(Split(argRange, " ")(0), ",", ", ") End Function Way to go. |
Inserting and deleting spaces with a macro
You can use worksheet functions to do the same.
=CONCATENATE(LEFT(A1,FIND(",",A1,1))," ",MID(A1,FIND(",",A1,1)+2,FIND(" ",A1,FIND(",",A1)))) if A1 contains the name. "crowdx42" wrote in message ... Ok, so I have names in a column which I want to both insert a space after a "," and then delete everything after the second word space. i.e James,Patrick Coogan needs to be James, Patrick Can this be done? Thanks in advance You guys have been very patient and I very much appreciate all the help. Patrick:) -- crowdx42 ------------------------------------------------------------------------ crowdx42's Profile: http://www.excelforum.com/member.php...o&userid=37749 View this thread: http://www.excelforum.com/showthread...hreadid=574054 |
Inserting and deleting spaces with a macro
Hi Try this one
A1: James,Patrick Coogan B1: =SUBSTITUTE(LEFT(A1,FIND(" ",A1)),",",", ") "crowdx42" wrote: Ok, so I have names in a column which I want to both insert a space after a "," and then delete everything after the second word space. i.e James,Patrick Coogan needs to be James, Patrick Can this be done? Thanks in advance You guys have been very patient and I very much appreciate all the help. Patrick:) -- crowdx42 ------------------------------------------------------------------------ crowdx42's Profile: http://www.excelforum.com/member.php...o&userid=37749 View this thread: http://www.excelforum.com/showthread...hreadid=574054 |
Inserting and deleting spaces with a macro
You could select your range
edit|replace what: (spacebar)* (two characters total) with: (leave blank) replace all edit|Replace what: , with: ,(spacebar) replace all If you needed code: Option Explicit Sub testme() With Selection .Replace What:=" *", Replacement:="", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False .Replace What:=",", Replacement:=", ", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False End With End Sub crowdx42 wrote: Ok, so I have names in a column which I want to both insert a space after a "," and then delete everything after the second word space. i.e James,Patrick Coogan needs to be James, Patrick Can this be done? Thanks in advance You guys have been very patient and I very much appreciate all the help. Patrick:) -- crowdx42 ------------------------------------------------------------------------ crowdx42's Profile: http://www.excelforum.com/member.php...o&userid=37749 View this thread: http://www.excelforum.com/showthread...hreadid=574054 -- Dave Peterson |
Inserting and deleting spaces with a macro
Excellent stuff, Dave's worked perfect for me, saved me a lot of time. Thanks so much :) Patrick -- crowdx42 ------------------------------------------------------------------------ crowdx42's Profile: http://www.excelforum.com/member.php...o&userid=37749 View this thread: http://www.excelforum.com/showthread...hreadid=574054 |
All times are GMT +1. The time now is 11:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com