Newbie - How to Remove An Extra Character at the Beginning of a Cell?

[Yellowbird]

I have data in the format:

00000
00001
00002
00003

and so on that was imported from a Crystal Report, which is pulling
these values from an SQL database. Once in Excel, I want these to be
no longer than 4 digits, as in:

0000
0001
0002
0003

So I need to remove the first zero that precedes each value. Once
properly formatted in Excel, I need to export my file as CSV and each
column must be a certain variable width (in this case, 4 characters).

I've looked into the Trim function, but I'm not sure how to trim just
the first character on the left.

Guidance or suggestions appreciated.

Thanks in advance,
Yellowbird

 

[firefytr]

a few options, how about...

=right(a1,3)
or
=mid(a1,2,3)
or
a custom format "000

 

[Dave R.]

OK if you want a function, use

=RIGHT(A1,LEN(A1)-1)

to get all the but leftmost character in a text string.

 

[Dave R.]

.... after that formula, you would copy/paste as special/values, then you can
export as CSV.

 

[Yellowbird]

Thanks for the suggestion, Dave.

I guess I really am a newbie, because I'm not sure what you mean. When
I export from Crystal Reports to Excel, a new worksheet is opened. Do
I then need to go to the column that has the issue and insert the
function, or is there a way to maybe create a macro or other automated
method of cleaning things up? The data is really only passed through
Excel on its way to CSV, so I hadn't intended to do much within Excel
except some minimal cleanup such as removing this extra character and
fixing a few currency fields.

Perhaps you could explain what you mean by "copy/paste as
special/values" before exporting?

Thanks again.
Yellowbird

 

[Dave R.]

Yellowbird,

I understand what you're trying to do. When Excel opens a new sheet, you
need to make slight changes to that sheet before exporting to CSV. Let's say
your data (which you want to remove the left 0 from)begins in A1 and goes to
A500. Take that formula from below and paste it into B1, and copy it down to
B500. This will give you your original list (A1:A500) next to the list with
the correct format (the leftmost character removed).

Select the range B1:B500, and Ctrl-C to copy. Then click on A1, go to
Edit>Paste Special> and check "values". This will paste the new list over
the old list. You can then delete B1:B500, since it's now contained in
A1:A500.

Maybe you don't want to use B1:B500, but you can use any empty column.. and
of course adjust the A1 in the formula

=RIGHT(A1,LEN(A1)-1)

to point to the first row of your data you want to remove the first
character from.

Yes it is possible to do a macro to remove the leftmost character--but there
are others better suited at providing advice on that. It would save you some
time if you're going to be doing this a lot. What I don't know is if a macro
can only be contained in a workbook -- meaning that when crystal reports
creates a new workbook, that workbook wouldn't contain any macros. But
perhaps it is possible to have a macro available in Excel no matter what
workbook you happen to be in when you want to run the macro. Like I said
there are others who can help you with that if you want to go that route.