Remove blanks from a string of numbers

Folks,
I have many numbers such as the one below where I need
13  04  2012
to be this
13042012
Frank FreeseAsked:
Who is Participating?
 
Missus Miss_SellaneusCommented:
Use SUBSTITUTE.

=SUBSTITUTE(text,old_text,new_text,instance_num)

This will do what you want.
=SUBSTITUTE(B2," ","")
0
 
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, " ","")
    Next
    
End Sub

Open in new window

0
 
Saqib Husain, SyedEngineerCommented:
Or

Select the range of cells to be converted
Press Ctrl-H
In "Find what" type a space
Click on "Replace all"
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
AlanConsultantCommented:
Hi,

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:

=VALUE(SUBSTITUTE(A1," ",""))

Hope that helps,

Alan.
0
 
Frank FreeseAuthor Commented:
Thank you folks - not bad - one question with four solutions.
Appreciate EE
0
 
AlanConsultantCommented:
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:

DDMMYYYY

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

HTH,

Alan.
0
 
Frank FreeseAuthor Commented:
That Alan
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.

All Courses

From novice to tech pro — start learning today.