• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 517
  • Last Modified:

excel find empty space and replace with a - character

I have excel 2010 and was wondering what macro would run on like cells B2-B60 and replace spaces with this character
0
techdrive
Asked:
techdrive
1 Solution
 
Jaffa0Commented:
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
 
NBVCCommented:
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
 
gowflowCommented:
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
 
techdriveAuthor Commented:
awesome
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Tackle projects and never again get stuck behind a technical roadblock.
Join Now