Formula - insert space after each instance of a specific character

I need to create a formula that looks at a given cell, finds all instances of a specific character, and does a substitute or replaement.

I need to have a formula that inserts a space after each comma....one that either replaces the "," with ", " or that just finds the comma and inserts a space.

For example:
    cell might show the following - First National Bank,Anytown,US,12345
    Needs to show as First National Bank, Anytown, US, 12345
IO_DorkAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
=substitute(a1,",",", ")
0
 
Saqib Husain, SyedEngineerCommented:
If you end up with commas followed by two spaces then try this

=substitute(substitute(a1,",",", "),",  ",", ")
0
 
IO_DorkAuthor Commented:
the amount of commas could and may vary...is there a formula to address this variability?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
barry houdiniCommented:
SUBSTITUTE will replace all the commas, so the number doesn't matter

You can address the double space issue with TRIM, i.e. amending Saqib's suggested formula like this

=TRIM(SUBSTITUTE(A1,",",", "))

regards, barry
0
 
Ess KayEntrapenuerCommented:
CTRL+R
0
 
IO_DorkAuthor Commented:
Thanks, worked perfectly with no extra spaces.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.