Clash Royale CLAN TAG

Find & Replace functionality automatically formats cells to dates

Oftentimes when converting PDFs to excel documents, some values are distorted and inaccurate. Precisely, some cells within my "Date" Column do not contain the correct month, grammatically. For example, instead of "XX Jun", it is "XX Iun", where "XX" represents a day (number).

XX Jun

XX Iun

I've resorted to use the Find & Replace functionality and it almost works perfectly, with the only caveat being that, after replacing, my cells are formatted to dates when I want them to remain as texts.

What I'd therefore like resolved, if possible, is to use the Find & Replace functionality without it formatting my cells to dates after replacing, by modifying my VBA code.

Sub Macro2()

Dim rng As Range

Set rng = Application.InputBox("Select Date Column", "Obtain Range", Type:=8)

rng.Replace What:="ian", Replacement:="Jan"
rng.Replace What:="lan", Replacement:="Jan"
rng.Replace What:="iun", Replacement:="Jun"
rng.Replace What:="lun", Replacement:="Jun"

End Sub


For clarity, some screenshots of what happens when I execute my code.

From This:

To This:

rng.NumberFormat = "@"

'

rng.Replace What:="ian", Replacement:="'Jan"

2 Answers
2

Rather than calling Range.Replace, keep your original column, and use another column to call a User Defined Function macro to "fix" the bad OCR value. This function would take in a single argument, the range with the "bad" date, and return a string. Something like this:

Public Function FixDate(ByVal s As String) As String
s = Replace$(s, "ian", "Jan")
[...other replacements...]
FixDate = s
End Function


Then call it like this:

=FIXDATE(A2)



=FIXDATE(A2)


This will have the advantage of keeping your original value in column A for QA/QC, and it won't auto-convert to a date value.

Rather than using the built-in VBA Replace() function, you can use the Regular Expression library (requires a reference) to streamline the various replacements, though this may incur a performance hit.

Replace()

This works for me:

Sub Macro2()

Dim rng As Range, v, c

Set rng = Application.InputBox("Select Date Column", "Obtain Range", Type:=8)

With rng
.NumberFormat = "@"
For Each c In .Cells
v = c.Value
v = Replace(v, "ian", "Jan")
v = Replace(v, "Ian", "Jan")
v = Replace(v, "iun", "Jun")
v = Replace(v, "Iun", "Jun")
c.Value = v
Next c
End With

End Sub


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.