CODEWITHSUNDEEP
excelvbastring-formatting
user2432812
user2432812

Reputation: 1

Replace text in a Range with a number having leading zeroes

I have code in an Excel macro to change some text. For example, change

Text 111

to

001

The code runs, and changes the text but I am losing the leading zeroes. I want to keep the leading zeroes. I have tried a number of things, like 

jRange.NumberFormat = "###"
.Replace "Text 333", "003", xlWhole, ReplaceFormat:=True

I also tried to set the format on the column like:

jRange.Columns("G").NumberFormat = "###"

Here is sample code

Sub FixIt()
Dim jRange As Range
Set jRange = ActiveSheet.Range("G2:G1000")


With jRange

.Replace "Text 111", "001", xlWhole
.Replace "Text 222", "002", xlWhole
.Replace "Text 333", "003", xlWhole
.Replace "Text 444", "077", xlWhole

End With
End Sub

Upvotes: 0

Views: 879

Answers (2)

user4691433
user4691433

Reputation:

I know this is an old question, but to answer it more correctly, the correct change would have been to change your previous attempt, .NumberFormat = "###", to .NumberFormat = "000". 0s act as always visible guidelines in .NumberFormat, whereas #s act as "if necessary" guidelines.

Upvotes: 0

Bathsheba
Bathsheba

Reputation: 234715

As a starting point, format the whole worksheet as text. Do this by clicking the small button to the left of the column headings and just above the row headings. This will select the whole worksheet.

Then, on the home tab, choose 'Text' in the 'Number' group.

That will do it, if a little draconian.

Another way, if the new cells are not part of the calculation, is to prefix each number with a single quote; e.g.

.Replace "Text 333", "'003", xlWhole, ReplaceFormat:=True

Upvotes: 2

Related Questions