Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

excel find empty space and replace with a - character

Posted on 2014-02-20
4
Medium Priority
?
496 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 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

Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
Measuring Server's processing rate with a simple powershell command. The differences in processing rate also was recorded in different use-cases, when a server in free and busy states.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot 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ā€¦

927 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