Solved

excel find empty space and replace with a - character

Posted on 2014-02-20
4
438 Views
Last Modified: 2014-02-20
I have excel 2010 and was wondering what macro would run on like cells B2-B60 and replace spaces with this character
0
Comment
Question by:techdrive
4 Comments
 
LVL 6

Expert Comment

by:Jaffa0
ID: 39873327
You could just select the area, press ctrl-H and replace " " with "-". If you record it as a macro that will give you the code, if you need to reuse it.
0
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39873332
Try:

Sub Macro1()

    Range("B2:B60").Replace What:=" ", Replacement:="-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End Sub

Open in new window


but you don't really need a macro.  Just select the range hit CTRL+H, and enter a space in Find What field, enter a dash in Replace With field, click Replace All
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39873340
Put this formula in C2 and drag it down till C60 you will get the space replaced by -
=REPLACE(B2,FIND(" ",B2),1,"-")

gowflow
0
 

Author Closing Comment

by:techdrive
ID: 39873449
awesome
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This article will help you understand what HashTables are and how to use them in PowerShell.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now