Solved

excel find empty space and replace with a - character

Posted on 2014-02-20
4
466 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 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 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

IoT Devices - Fast, Cheap or Secure…Pick Two

The IoT market is growing at a rapid pace and manufacturers are under pressure to quickly provide new products. Can you be sure that your devices do what they're supposed to do, while still being secure?

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Auditing domain password hashes is a commonly overlooked but critical requirement to ensuring secure passwords practices are followed. Methods exist to extract hashes directly for a live domain however this article describes a process to extract u…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

623 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