Stripping spaces from a number in Excel ...

[TheBionicOrange]

I've got a column of numbers.
Each number is 19 digits long, and there are NO leading zeros.

Unfortunately, these numbers have intermittent spaces in the middle of them which I need to strip out.

e.g. 292 393 67675363 98769

I tried using the INSTR function, but that was giving me some weird results.

This is pretty urgent, so any input would be greatly appreciated.

Cheers

Steve


p.s. I have posted in the VBA forum, but I need an answer pretty quick and no-one is in there

[Bruce Fox]

G'Day TheBionicOrange,

Can you use Replace whilst iterating past each Row, like:

MsgBox Replace("292 393 67675363 98769", " ", "")

Bruce.

[Bruce Fox]

Steve,

This will iterate past Column A and do all used rows:

Dim intRowMax As Integer, intRowIdx As Integer
 
intRowMax = ActiveSheet.UsedRange.Rows.Count
 
For intRowIdx = 1 To intRowMax
     ActiveSheet.Range("A" & intRowIdx).Value = Replace( ActiveSheet.Range("A" & intRowIdx).Value, " ", "")
Next

Bruce.

[TheBionicOrange]

Cheers Bruce (why am I now thinking of Finding Nemo !!!)

Always tend to overlook the obvious when I'm in a flap


[edit] Thanks for that extra bit too [/edit]

[Bruce Fox]

Know what you mean

Good to see you back on the ol (VB) forum.



Cheers,
Bruce.