lacy

  • Thread starter Thread starter lacy
  • Start date Start date
L

lacy

I have a column of sedols (sedols are security identifies) that is about 8000
rows long and will be refreshed with new data every day. Sedols need to be 8
characters long. The problem is, when I drop a sedol into excel that starts
with one or more zeros, excel truncates the zeros which then leaves me with
an invalid sedol. At that point I need to go in and change for example
“3128†to “ ‘0003128â€. Generally, there about 200 or so of these out of
8000. Was wondering if there was a way to create a macro that would look at
all sedols in the range, check if they are 8 characters, and, if not, add
enough zeros as prefix to get to 8 characters…

thanks!
 
Hi Lacy,

It looks like the operation you currently run is converting the numbers to
text. Try recording a Macro that performs text to columns on the column in
question (Data>Text to columns...). In the format section (on step 3 of 3) of
this process, select text.

This will convert the entire column to text though, and without seeing your
data I can have no idea what affect this might have on other processes.

But recording the text to columns process will give you the code you need.

Regards
Dave
 
Hi Lacy,

It looks like the operation you currently run is converting the numbers to
text. Try recording a Macro that performs text to columns on the column in
question (Data>Text to columns...). In the format section (on step 3 of 3) of
this process, select text.

This will convert the entire column to text though, and without seeing your
data I can have no idea what affect this might have on other processes.

But recording the text to columns process will give you the code you need.

Regards
Dave
 
select your cells
then CTRL+1 and define custom format 00000000


pls click YES if it helped
 
select your cells
then CTRL+1 and define custom format 00000000


pls click YES if it helped
 
Select the column>Rightclick>FormatCells>Custom> Type eight zeroes as below
and OK

00000000

If this post helps click Yes
 
Select the column>Rightclick>FormatCells>Custom> Type eight zeroes as below
and OK

00000000

If this post helps click Yes
 
Back
Top