find & replace - the missing leading zero problem

[yky]

I use the following code to find and replace things like 0101.1 to 0101.A. The problem is after find & replace, what I get is 101.A, not 0101.A. The leading zero is gone. How do I preserve the leading zero? The cell is formatted as text. Thanks.


Range(Cells(6, 9), Cells(5330, 9).End(xlUp)).Select
Selection.Replace what:=".1", replacement:=".A", lookat:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False

 

[Rick Rothstein]

How are you maintaining the leading zero in your original data? I've tested your code on cells formatted as Text and formatted as General where the value in the cell starts with an apostrophe (in order to be treated like text) and, in both case, that leading zero is retained. So I am not sure how you are set up that you are losing it.

 

[yky]

How are you maintaining the leading zero in your original data? I've tested your code on cells formatted as Text and formatted as General where the value in the cell starts with an apostrophe (in order to be treated like text) and, in both case, that leading zero is retained. So I am not sure how you are set up that you are losing it.

Thanks for the reply.

I formatted the cells as text by choosing "format/format cells.../text".

OK. I have to confess. Actually, I convert .A to .1 first, sort the column, then convert .1 back to .A. But I don't think sorting would have any effect on the leading zero.

The following is a real example of what I got:

051
056
070
075
80.A
81.B

 

[Jonmo1]

OK. I have to confess. Actually, I convert .A to .1 first

Here's the problem...
Using Find/Replace to change .A to .1 converts the value to a number (regardless of how the cell is formatted)
Leading 0's are meaningless to a number, so Excel Drops them.

What is the whole process, and what do you want to achieve?
There's likely a much better way.

 

[yky]

Here's the problem...
Using Find/Replace to change .A to .1 converts the value to a number (regardless of how the cell is formatted)
Leading 0's are meaningless to a number, so Excel Drops them.

What is the whole process, and what do you want to achieve?
There's likely a much better way.

If I sort with .A, it will end up at the bottom and be out of order. I want the sorted order to be something like 0305, 0401.A, 0501, not 0305, 0501, 0401.A.

 

[Jonmo1]

So you want it sorted by the numbers, ignoring the text parts?

Is the text on the end ALWAYS just 1 digit?

You can put a formula in another column to extract the numerical parts and sort by that column...

 

[Rick Rothstein]

OK. I have to confess. Actually, I convert .A to .1 first

That is an important "confession"... as jonmo1 has pointed out, it is where the leading zero gets removed at, not at the point in your code that you indicated in your original message.