Stripping spaces from a number in Excel ...

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

G'Day TheBionicOrange,

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

VB Code:

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

Bruce.

Steve,

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

VB Code:

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.

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]

Know what you mean :)

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



Cheers,
Bruce.