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!

lacy
 

Please use meaningful subject lines.
Just format your cells as 00000000

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"lacy" wrote in message
...
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!

lacy
 

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 (DataText 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

"lacy" wrote:

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!

lacy
 

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


pls click YES if it helped


On 22 Maj, 15:01, lacy wrote:
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!

lacy
 

Select the columnRightclickFormatCellsCustom Type eight zeroes as below
and OK

00000000

If this post helps click Yes
---------------
Jacob Skaria


"lacy" wrote:

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!

lacy
 

Right Click
Format cells
Custom Format
00000000

Now ever time you enter a number it will be 8 digits long, with leading 0s if
required

edvwvw

lacy wrote:
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!

--
Message posted via http://www.officekb.com