KEEP ONE ZERO.

  • Thread starter Thread starter John
  • Start date Start date
J

John

Is there and fromula or macro to keep only one zero after the N.
sample: in column A at the last row the N# like belowe:
N0069, N00125, N0002509 ,N002609 or N000023004
just want to keep one zero between the N to the number.
 
You could do it with Find & Replace - highlight the column, then CTRL-
H:

Find what: N0000
Replace with: N0
Replace All

CTRL-H again:
Find what: N000
Replace with: N0
Replace All

CTRL-H again:
Find what: N00
Replace with: N0
Replace All

If you want a formula to do it, look in Excel Help for the SUBSTITUTE
function.

Hope this helps.

Pete
 
You could always set a custom cell format "N0"# and then just imput the
number. The number would then change to the format you want
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)
 
One way:
A1 = N000023004

="N0"&--MID(A1,2,255)

Returns: N023004
Click to expand...

On the off-chance that the first letter could possibly be something other
than an "N" and that the second character could possibly be something other
than "0", then this modification to your formula could be used...

=LEFT(A1,1+(MID(A1,2,1)="0"))&--MID(A1,2,255)

Rick
 
Back
Top