Inserting and deleting spaces with a macro

[crowdx42]

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

[NickHK]

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" <crowdx42.2cxhbd_1156227604.3714@excelforum-nospam.com> wrote in
message news:crowdx42.2cxhbd_1156227604.3714@excelforum-nospam.com...
>
> 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
>

[moon]

=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" <crowdx42.2cxhbd_1156227604.3714@excelforum-nospam.com> schreef
in bericht news:crowdx42.2cxhbd_1156227604.3714@excelforum-nospam.com...
>
> 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
>

[moon]

"NickHK" <TungCheWah@Invalid.com> schreef in bericht
news:%23ZQmC3bxGHA.1272@TK2MSFTNGP05.phx.gbl...
> 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.

[Jude]

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" <crowdx42.2cxhbd_1156227604.3714@excelforum-nospam.com> wrote in
message news:crowdx42.2cxhbd_1156227604.3714@excelforum-nospam.com...
>
> 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
>

[Muhammed Rafeek M]

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
>
>

[Dave Peterson]

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

[crowdx42]

Excellent stuff,
Dave's worked perfect for me, saved me a lot of time.
Thanks so much
Patrick