Add apostrophe to a number with leading zero's - but keep leading zero's

[mrwul62]

Within a range (column, containing both text and numbers) I have numbers displayed like:
0001
0010
etc.

Cell properties -> custom show: 0000

I would like to add an apostrophe to those cells.
However .. the result should still be that the leading zero's are displayed, i.e. the result would be as if I were to manually enter '0001

Is there a macro that can do this?

Thanks!

 

[mikerickson]

Try the custom format "'"0000


That is quote apostrophe quote zero zero zero zero

 

[mrwul62]

Try the custom format "'"0000


That is quote apostrophe quote zero zero zero zero

With me it then shows '0001, i.e. the apostrophe is displayed (that is not ideal)
If I were to manually enter : '0001 then the apostrophe is not displayed (except it is shown at the top, right of the formula bar).
That is the way I'd like it to have.
(There will be this small exclamation triangle showing up)

 

[sandy666]

try:

=IFERROR(BASE(A1,10,4),A1)

alpha	alpha
1​	0001
beta	beta
2​	0002

 

[Peter_SSs]

See if this does what you want. Test with a copy of your data.

Sub Add_Apostrophe()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(#="""","""",TEXT(#,""'0000""))", "#", .Address))
  End With
End Sub

 

[mrwul62]

See if this does what you want. Test with a copy of your data.

Sub Add_Apostrophe()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(#="""","""",TEXT(#,""'0000""))", "#", .Address))
  End With
End Sub

That looks great indeed!
Thanks.

One thing though: noticed that this is limited to 1 range (column A). If it is column B, C, D or even E, is there a way to 'expand' the range within this macro.
As a workaround I can, of course, copy a specific column to a new sheet and paste it in column A, run the macro and the copy paste it back to where it came from...

Thanks again.

 

[Peter_SSs]

That looks great indeed!
Thanks.

You're very welcome.

is there a way to 'expand' the range within this macro.

Sure. Suppose you want to apply it to the range from cell H1 to the last filled cell in column K, then just change this line

With Range("H1", Range("K" & Rows.Count).End(xlUp))

.. or you might have a specific range in mind

With Range("M5:AC100")

 

[mrwul62]

Sorry, my mistake, what I meant to say is that I would like the macro to be applied on 1 column only (not necessarily a range) but the column can be different, i.e. it can be A or B or C, or ...
Right now I believe the macro can be applied on column A only.
As said, as a workaround, I might copy, let's say, column D (needed to be 'adjusted') to a temporary file and paste in column A, then apply the macro and copy pate the column back to the source column being column D.

 

[Peter_SSs]

I would like the macro to be applied on 1 column only (not necessarily a range) but the column can be different, i.e. it can be A or B or C, or ...

Then just change the As in the code to whatever column you want to apply it to.

Sub Add_Apostrophe()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(#="""","""",TEXT(#,""'0000""))", "#", .Address))
  End With
End Sub

 

[mrwul62]

Thanks again!
Really super.