Lost spaces and Zeros lost when replacing text

**renshawk** · 09-28-2013, 05:06 PM

Hi

This one has me confused

When the data in the Cell is "xxxx: 05555" the result following the replace is "5555" where I would expect " 05555" , I would appreciate any Ideas to fix this;

ActiveSheet.Columns("B").NumberFormat = "@"
ActiveSheet.Columns("B").Replace What:="*:", Replacement:=""

Thanks Kev ( using Excel 2003 )

**AlKey** · 09-28-2013, 05:49 PM

You can simply go to Forma Cells, select Custom and type in 00000
Or if you want to use use formula you can use this:

=TEXT(A1,"00000")

**Philb1** · 09-29-2013, 12:54 AM

Hi Renshawk
Try this

Please Login or Register  to view this content.

**renshawk** · 09-29-2013, 04:50 AM

Many thanks - this wont preserve the space and corrupts numbers of different length or format - e.g 05 and 55 are then changed - I realy need the Replace - to treat the cell as text.