0

I would greatly appreciate some assistance in figuring out how to remove the spaces between words. I will only need to target whatever is in cell "A1" for example if I have "Next Ex" in the cell I want the macro to change it to "NextEx". I will run this on multiple sheets that's why a formula would not be my best option.

5
  • Use use SUBSTITUTE() or REPLACE() in a loop.
    – BruceWayne
    Jul 26, 2017 at 15:56
  • That's for the response. How would the code look like because I'm not familiar on using either one. Thanks.
    – Eric
    Jul 26, 2017 at 16:05
  • 1
    To get you started, have a read through some of the various questions on using loops in VBA: superuser.com/search?q=vba+loop+through+worksheets. You will need to tweak the code - have a bash and if you're struggling edit your question to ask something specific about the bit of code that isn't doing what you need.
    – Andi Mohr
    Jul 26, 2017 at 16:13
  • (1) Another good starting place is Excel’s built-in help.  (2) Do you want to actually change values in cells?  If so, a formula is not an option at all.  You should understand that, and it helps us all if you are clear about your requirements.  (3) Is your question more complicated than you have described, or less?  If you want to remove all the spaces in a text value, just say that.  “remove the spaces between words” sounds like it could potentially be something more complicated and subtle. … (Cont’d) Jul 26, 2017 at 17:37
  • (Cont’d) …  (4) If you want to take the value in A1 and display it with all the spaces removed in some other cell (such as A2 or Q42) on every sheet, that doesn’t require a loop and can easily be done with a formula.  The better you describe your requirements, the better we can answer you. Jul 26, 2017 at 17:38

3 Answers 3

2

Consider:

Sub NoSpace()
    Dim sh As Worksheet
    For Each sh In Sheets
        sh.Range("A1").Replace what:=" ", replacement:=""
    Next sh
End Sub
0
1

VBA:

Dim cellText As String
cellText = [Get Cell Text From Worksheet]
cellText = Replace(cellText," ", "")

Formula:

=SUBSTITUTE(A1," ","")
1

VBA:

Join(Split(Range("A1").Value, " "), "")

Split (doc) string into array using " " (space) as delimiter; Join (doc) array into string using "" (empty string) as delimiter.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .