?
Solved

excel find empty space and replace with a - character

Posted on 2014-02-20
4
Medium Priority
?
483 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2000 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 31

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the adminiā€¦

719 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