2

I have column in excel with dates of birth of employees (in format DDMMYYYY) and for some reason I was asked to add apostrophe before each and every record (in a way that it is not visible). There is a lot of records. When I try to do It automatically it always removes leading zero. For example from 01011900 I get 1011900.

I tried find & replace, concatenate,

Nothing from these answers helped: Adding Appostrophe in every field in particular column for excel

Each cell has to be formatted as a text, not date.

Any ideas?

Community
  • 1
  • 1
thewilq
  • 25
  • 1
  • 6

3 Answers3

4

Try:

="'"&TEXT(A1,"00000000")

This will force a format of "00000000" which will allow leading zeros. In fact, if you use =TEXT() then you don't need the apostrophe to force the text value.

VBA version: 

Sub MacroMan()

Dim rng As Excel.Range

Set rng = Application.InputBox("Select range to amend:", , , , , , , 8)

If Not rng Is Nothing Then
    For Each cell In rng.Cells
        cell.Value = "'" & Format(cell.Value, "00000000")
    Next
End If

End Sub
SierraOscar
  • 17,507
  • 6
  • 40
  • 68
  • I know that I don't need it but the requestor do not know and I cannot explain it to them(they are dumb). Your solution is not what I am looking for though because the apostrophe is visible, I need to achieve something like you would get putting apostrophe manually(It's there but not visible with green mark in top left corner of the cell), but there is just too many records to do it manually. – thewilq Aug 26 '15 at 09:19
  • Okay, see VBA version above that will do it for you. Just run the code and select the range that you want to amend. – SierraOscar Aug 26 '15 at 09:37
  • Thanks for your inputs but I found out very simple solution. I didn't know that invisible apostrophe is part of "cell format". I just added one manually and then pasted format from that cell for whole column. Now all records are text with invisible apostrophes and leading zeros(if ther is any). – thewilq Aug 26 '15 at 10:32
1

A way around is simply by changing the last digit of the year (assuming the data is all of the same year, for example 2018 replace with 201, using find and replace menu. Or if the case is multiple years, we could replace the first 2 digits of the year with 3 digits which the last digit is a particular digit, say 0. Thus the year /1988 would become /19088 after replacement. Second phase is by adding aposthrope to the top cell, and simply paste its format to other cells. Last phase is replace the wrong year to its original data. Hope could help.

user9713444
  • 11
  • 1
0

Right click the column and Format the cell. Choose custom and type ddmmyyyy or mmyydddd. That should do the trick.

Jayson
  • 157
  • 1
  • 10