Leading 'Text' zero's - comparing ranges

[Kobayashi]

Can anybody advise how I can remove any and all zeros from a th
BEGINNING of a field without countless IF functions?

I am concatenating three fields to make a unique value and the
comparing this to another similar concatenation. However, the data
have in one of the fields is a text serial number. It cannot b
converted to an actual number field because the values sometime
contain letters.

Now, the problem is that sometimes one of these values will have
'leading' zero in one of the ranges but not in the other.
As I said earlier, I am concatenating three fields in each range bu
I'm THEN performing a Match function so need to make any alteration
before this Match function runs. I'm thinking along the lines above o
just removing any zero's at the beginning but if there is a better wa
then great!

Always grateful for the assistance,

Adria

 

[Frank Kabel]

Hi
how are your values stored (as Text?)

 

[Smalltalk_vb]

if the number of leading zeros is fixed, that is N
characters long, then use mid(A1,3) to return the string
starting at the 3nd position up to the last character.

 

[Kobayashi]

Frank,

Yes, they are formatted as 'General'.

Regards,

Adria

 

[Kobayashi]

Frank,

I would have used the following that Tom Oglivy supplied on anothe
thread, substituting the " " for "0" but this won't capture the field
that may have more than one leading zero?

Sub RemoveSpace()
Dim cell As Range

For Each cell In Selection
If Not cell.HasFormula Then
If Not IsEmpty(cell) Then
If Left(cell.Value, 1) = " " Then
cell.Value = Right(cell.Value, _
Len(cell.Value) - 1)
End If
End If
End If
Next
End Su

 

[Kobayashi]

Smalltalk,

Thanks, but I'm afraid the number of characters/numbers is not fixed.
Some fields may have 9 numbers including a leading zero whilst other
may have 11 without.


Regards,

Adria

 

[Frank Kabel]

Hi
if these numbers are stored as Text try
=TEXT(--A1,"0")

 

[Tom Ogilvy]

Sub RemoveLeadingZeros()
Dim cell As Range
Dim sStr As String
For Each cell In Selection
If Not cell.HasFormula Then
If Not IsEmpty(cell) Then
sStr = cell.Text
Do While Left(sStr, 1) = "0" And Len(sStr) > 1
sStr = Right(sStr, _
Len(sStr) - 1)
Loop
If sStr <> "0" Then
cell.Value = "'" & sStr
Else
cell.ClearContents
End If
End If
End If
Next
End Sub

 

[Kobayashi]

Frank,

I appreciate the help but couldn't get that to work?


Tom,

Yet again, many thanks. Simply copied your code and plonked it in, mad
a few minor adjustments so that it picked up my already defined range
and hey presto! Works like a dream!

Can I ask why the apostrophe is being added though in the following
cell.Value = "'" & sStr? I'm only asking to aid my understanding s
that, one day, I can stop troubling you all and just maybe star
contributing myself!

Again, many thanks

 

[Tom Ogilvy]

I assume you want your entries to be text - if the entry would be all
numeric after removing the leading zeros, then when placed back in the cell
it would be converted to a number (in most cases). Using the leading "'"
will insure it remains text. You may not need it, so you can remove it if
that is the case.

 

[Kobayashi]

Tom,

Many thanks for the explanation. One last question though, does i
matter whether the cells are formatted as text or numbers? I mean, if
had one range of numbers formatted as numbers and then another range o
'numbers' formatted as text and tried to Match or compare would I ge
differing results?

Thanks,

Adria

 

[Tom Ogilvy]

Yes. 123 <> "123" as far as Match is concerned.

if you are checking in code, you can always do a conversion

for i = 1 to 10
for each cell in Range("B1:B10")
if isnumeric(cells(i,1)) and isnumeric(cell) then
if abs(cdbl(cells(i,1)) - cdbl(cell)) < .00000001 then
msgbox " Cells Match"
end if
elseif lcase(cells(i,1).Text) = lcase(cell.Text) then
msgbox " Cells Match"
else
msgbox " Cells don't match"
end if
Next
Next

 

[Kobayashi]

Tom,

Again, many thanks for not only taking the time to explain but fo
providing some code that I can use to capture differences i
formatting!
This really is going above and beyond and I'm very grateful indeed!

All the best,

Adria