How to remove spaces between words using vba xcel

Question

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.

Solution 1

Consider:

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

Solution 2

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.

Solution 3

VBA:

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

Formula:

=SUBSTITUTE(A1," ","")

Comments

[BruceWayne]

Use use SUBSTITUTE() or REPLACE() in a loop.

[Eric]

That's for the response. How would the code look like because I'm not familiar on using either one. Thanks.

[Andi Mohr]

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.

[Scott - Слава Україні]

(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)

[Scott - Слава Україні]

(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.