TRIM leading zero in strings

[Yellowbird]

Hi,

I have a column containing a five digit Line Number value (string), as
follows:

00000
00001
00002

and so on. I need this value to be no longer than 4 digits in length,
so I need to trim the leading zero from the string in each cell so
that I will have:

0000
0001
0002

I understand that the TRIM function will remove a leading space from a
string value, but I can't seem to get it to work for the leading zero.
What am I missing?

Thanks in advance,
Yellowbird

 

[Vasant Nanavati]

Just use:

=RIGHT(A1,4)

 

[Rob van Gelder]

The simple way is to format the column as 0000

 

[Yellowbird]

Thanks, Vasant.

I need to be able to apply the formula to the entire column and this
is data imported from a Crystal Report. The final data is then
exported from Excel to CSV, so the intermediary step is simply for
cleanup. I have a macro that does most of the cleanup work and I'd
like to add this step to it.

What I have is:

Columns("W:W").Select
Range("W2, W65536").End(xlUp).Offset(1, 0).Activate

This successfully selects the column, but I am unclear on how to
specify the formula for all of those selected cells.

=RIGHT(W:W,4) creates a circular reference.

Once all cells in the column are selected, I think I should be able to
apply a formula to trim the leading zero from the string in each cell
so that I will have:

0000
0001
0002
0003
0010
0012

and so on. Essentially I am making my 5 character cell value a 4
character value and removing the first character from every cell.

Hopefully I am being clear.

Thanks again for any assistance.

Yellowbird

 

[Vasant Nanavati]

In a macro, just use:

Dim c As Range
For Each c In Range(Range("W2"), Range("W65536").End(xlUp))
c.Value = Right(c.Value, 4)
Next