Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number Formating
Hello Everyone,
I have thousands of numbers that will be fed into a spreadsheet some of the numbers will be: five digits - five digits (e.g. 22222-22222) note: that is a dash not a minus some will be: 4 digits - 6 digits (e.g. 2222-222222) and some will be: 6 digits - 4 digits (e.g. 222222-2222) I want to separate the three from each other. i used a simple if statement: If ActiveSheet.Cells(rownum, 1).numberFormat = ####-###### Then yet it does not regonize the numbers on the sheet that are 4-6 digit numbers the format for that column is set up as general, and obviously going through all the numbers and individually setting up the format defeats the purpose of having the program do it....What am i doing wrong? Thanks a bunch, Jordan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number Formating
As you say the format is all General, and the numbers are text.
If you want to split it, why not just use DataText To Columns, with a separator of '-'? You could record a macro to do it for future use. -- HTH RP (remove nothere from the email address if mailing direct) "Jordan" wrote in message ... Hello Everyone, I have thousands of numbers that will be fed into a spreadsheet some of the numbers will be: five digits - five digits (e.g. 22222-22222) note: that is a dash not a minus some will be: 4 digits - 6 digits (e.g. 2222-222222) and some will be: 6 digits - 4 digits (e.g. 222222-2222) I want to separate the three from each other. i used a simple if statement: If ActiveSheet.Cells(rownum, 1).numberFormat = ####-###### Then yet it does not regonize the numbers on the sheet that are 4-6 digit numbers the format for that column is set up as general, and obviously going through all the numbers and individually setting up the format defeats the purpose of having the program do it....What am i doing wrong? Thanks a bunch, Jordan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number Formating
Hey Bob & others,
I tried doing that, unfortunatly that still wont tell my program if the first column is 4, 5 or 6 digits long, which is what i really need. also it could have letters such as: A01S-012355 Any more thoughts? Thanks, Jordan "Bob Phillips" wrote: As you say the format is all General, and the numbers are text. If you want to split it, why not just use DataText To Columns, with a separator of '-'? You could record a macro to do it for future use. -- HTH RP (remove nothere from the email address if mailing direct) "Jordan" wrote in message ... Hello Everyone, I have thousands of numbers that will be fed into a spreadsheet some of the numbers will be: five digits - five digits (e.g. 22222-22222) note: that is a dash not a minus some will be: 4 digits - 6 digits (e.g. 2222-222222) and some will be: 6 digits - 4 digits (e.g. 222222-2222) I want to separate the three from each other. i used a simple if statement: If ActiveSheet.Cells(rownum, 1).numberFormat = ####-###### Then yet it does not regonize the numbers on the sheet that are 4-6 digit numbers the format for that column is set up as general, and obviously going through all the numbers and individually setting up the format defeats the purpose of having the program do it....What am i doing wrong? Thanks a bunch, Jordan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number Formating
Hi Jordan,
See if this helps - Select Case InStr(ActiveSheet.Cells(rownum, 1), "-") - 1 Case 4 Case 5 Case 6 Case Else End Select Gary |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number Formating
ooh, thats a neat trick, thanks Gary
"GaryDK" wrote: Hi Jordan, See if this helps - Select Case InStr(ActiveSheet.Cells(rownum, 1), "-") - 1 Case 4 Case 5 Case 6 Case Else End Select Gary |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Number Formating
You can then do a
=LEN(A1) to get that value -- HTH RP (remove nothere from the email address if mailing direct) "Jordan" wrote in message ... Hey Bob & others, I tried doing that, unfortunatly that still wont tell my program if the first column is 4, 5 or 6 digits long, which is what i really need. also it could have letters such as: A01S-012355 Any more thoughts? Thanks, Jordan "Bob Phillips" wrote: As you say the format is all General, and the numbers are text. If you want to split it, why not just use DataText To Columns, with a separator of '-'? You could record a macro to do it for future use. -- HTH RP (remove nothere from the email address if mailing direct) "Jordan" wrote in message ... Hello Everyone, I have thousands of numbers that will be fed into a spreadsheet some of the numbers will be: five digits - five digits (e.g. 22222-22222) note: that is a dash not a minus some will be: 4 digits - 6 digits (e.g. 2222-222222) and some will be: 6 digits - 4 digits (e.g. 222222-2222) I want to separate the three from each other. i used a simple if statement: If ActiveSheet.Cells(rownum, 1).numberFormat = ####-###### Then yet it does not regonize the numbers on the sheet that are 4-6 digit numbers the format for that column is set up as general, and obviously going through all the numbers and individually setting up the format defeats the purpose of having the program do it....What am i doing wrong? Thanks a bunch, Jordan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formating a number in a cell | Excel Discussion (Misc queries) | |||
Number Formating | Excel Discussion (Misc queries) | |||
Change number formating | Excel Worksheet Functions | |||
Number formating | Excel Discussion (Misc queries) | |||
Formating a number | Excel Discussion (Misc queries) |