Remove blanks from a string of numbers

I have many numbers such as the one below where I need
13  04  2012
to be this
Frank FreeseAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Missus Miss_SellaneusCommented:


This will do what you want.
=SUBSTITUTE(B2," ","")

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Michael FowlerSolutions ConsultantCommented:
If the values are in column A this script will work. Just edit it to suit your needs or let me know and I will

Sub RemoveBlanks()
    Dim i As Long
    For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
        Range("A" & i).Value = Replace(Range("A" & i).Value, " ","")
End Sub

Open in new window

Saqib Husain, SyedEngineerCommented:

Select the range of cells to be converted
Press Ctrl-H
In "Find what" type a space
Click on "Replace all"
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.


If you use SUBSTITUTE on its own, you'll get a string out.  If that is what you want, then no problem, but if you want to return a number, then:


Hope that helps,

Frank FreeseAuthor Commented:
Thank you folks - not bad - one question with four solutions.
Appreciate EE
Already answered, but just in case...

If the cell entries are actually date values, then you could just format the cells with the following custom format:


(rather than DD MM YYYY)

Please note that will also have the advantage of leaving the cell values as valid dates (from excel's perspective).


Frank FreeseAuthor Commented:
That Alan
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.