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.

One way:

A1 = N000023004

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

Returns: N023004

--
Biff
Microsoft Excel MVP


"John" wrote in message
...
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

On Dec 7, 6:42 pm, John wrote:
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 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)


"John" wrote:

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.

One way:

A1 = N000023004

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

Returns: N023004

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