Number Formating

[Guest]

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

 

[Bob Phillips]

As you say the format is all General, and the numbers are text.

If you want to split it, why not just use Data>Text 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)

 

[Guest]

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

 

[GaryDK]

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

 

[Guest]

ooh, thats a neat trick, thanks Gary

 

[Bob Phillips]

You can then do a

=LEN(A1)

to get that value

--

HTH

RP
(remove nothere from the email address if mailing direct)