Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|