Replacing commas with ampersand

[Declamatory]

Hi Folks,

We have a new admin system. When the data was migrated to our new system the accounts that were held in joint names names came across with commas after each person's name. Where an account was previously held in joint names but now is an account held by a sole individual there is a comma after the person's name. We can have up to five joint holders on an account. We have ended up with accounts that looks like the following.

John Smith

David Anderson, Karen Anderson

Jeffrey Jones, Derek Jones, Andrew Jones, Kevin Jones, Andrea Jones

Karl Brown,

William Downing, Blake Downing,

Using Microsoft CRM we can export the client names into excel, tidy them up and import them back into CRM. We would like each account to have an ampersand between account holders so the data would look as follows:

John Smith

David Anderson & Karen Anderson

Jeffrey Jones & Derek Jones & Andrew Jones & Kevin Jones & Andrea Jones

Karl Brown

Is there a formula or VBA code that would solve this?

Thanks in advance

 

[James006]

Hello,

Have you tested the Substitute() function ...???

 

[Declamatory]

But if I substituted , with & I would end up with some accounts where the last client or a sole account would have an ampersand after there name so it would look like "William Downing & Blake Downing &"

 

[Peter_SSs]

Try

Excel Workbook
	A	B
1	John Smith	John Smith
2	David Anderson, Karen Anderson	David Anderson & Karen Anderson
3	Jeffrey Jones, Derek Jones, Andrew Jones, Kevin Jones, Andrea Jones	Jeffrey Jones & Derek Jones & Andrew Jones & Kevin Jones & Andrea Jones
4	Karl Brown,	Karl Brown
5	William Downing, Blake Downing,	William Downing & Blake Downing
Replace comma

 

[Yongle]

or perhaps

=TRIM(SUBSTITUTE(SUBSTITUTE(A2,", "," & "),",",""))


or

=SUBSTITUTE(SUBSTITUTE(TRIM(A2),", "," & "),",","")

 

[Declamatory]

Thanks for your answers. The first two suggestions left me with clients that had an ampersand after their name.

William Downing, Blake Downing, resulted in William Downing & Blake Downing &

The final suggestion worked a treat. Thank you for your assistance. It's much appreciated

 

[Peter_SSs]

The first two suggestions left me with clients that had an ampersand after their name.

Sounds like there must have been some space characters after the final comma that we couldn't see.
Anyway, glad you got something that worked.