Excel / VBA Left Function (Fine-tuning)

[Sambora]

I'm trying to extract the first three characters in a cell and have found this which works for numbers and text apart from where the cell begins with a 0 or multiple 0's

Sub CompareScheme()
Dim SourceRange As Range
Dim DestinationRange As Range
Dim Z As Integer

Set SourceRange = Range("A1:A10")
Set DestinationRange = Range("D1:D10")

For Z = 1 To SourceRange.Count
DestinationRange(Z, 1).Value = Left$(SourceRange(Z, 1).Value, 3)
Next Z
End Sub

For example, if a cell is 0045845, I want it to bring back 004 and not just 4. I thought if I changed the = Left$(SourceRange(Z, 1).Value, 3) to = Left$(SourceRange(Z, 1).Text, 3) then this might solve it but it makes no difference to column D. I've almost got it how I want it, but unless I have Column D in the right format, I can't do all of the analysis that I would want.

Any help would be greatly appreciated.

 

[Rick Rothstein]

Try adding this line of code right before your For loop...

DestinationRange.NumberFormat = "@"

 

[Sambora]

Perfect!!! Thanks for your help.