Solved

Formula - insert space after each instance of a specific character

Posted on 2013-12-17
6
246 Views
Last Modified: 2013-12-17
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
0
Comment
Question by:IO_Dork
6 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 39724796
=substitute(a1,",",", ")
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39724800
If you end up with commas followed by two spaces then try this

=substitute(substitute(a1,",",", "),",  ",", ")
0
 

Author Comment

by:IO_Dork
ID: 39724842
the amount of commas could and may vary...is there a formula to address this variability?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 50

Expert Comment

by:barry houdini
ID: 39724852
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
 
LVL 15

Expert Comment

by:Ess Kay
ID: 39724906
CTRL+R
0
 

Author Closing Comment

by:IO_Dork
ID: 39724934
Thanks, worked perfectly with no extra spaces.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question