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

mrwul62

Board Regular
Joined
Jan 3, 2016
Messages
61
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Try the custom format "'"0000


That is quote apostrophe quote zero zero zero zero
 
Last edited:
Upvote 0
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)
 
Upvote 0
try:
Code:
=IFERROR(BASE(A1,10,4),A1)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td]alpha[/td][td]alpha[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1​
[/td][td]0001[/td][/tr]

[tr=bgcolor:#FFFFFF][td]beta[/td][td]beta[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
2​
[/td][td]0002[/td][/tr]
[/table]
 
Last edited:
Upvote 0
See if this does what you want. Test with a copy of your data.
Code:
Sub Add_Apostrophe()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(#="""","""",TEXT(#,""'0000""))", "#", .Address))
  End With
End Sub
 
Upvote 0
See if this does what you want. Test with a copy of your data.
Code:
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.
 
Upvote 0
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
Code:
With Range("H1", Range("K" & Rows.Count).End(xlUp))
.. or you might have a specific range in mind
Code:
With Range("M5:AC100")
 
Last edited:
Upvote 0
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.
 
Upvote 0
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. :)

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

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top