Excel Converting number incorrectly

[Mister H]

Hi:

I am working on a spreadsheet and when I type in a long number Excel is converting the number. I try keying:

123123456123412345612345612341234

Excel populates the cell with: 1.23123456123412E+32 or futher shortens that to 1.23E+32

Can this feature be turned off. I am trying to get a range of cells to format that number as:

123-123456-1234-123456-123456-1234-1234

I have a code that was supplied to me but as mentioned above Excel is changing my number. I do not want to have to key ' before each number.

Thanks,
Mark

 

[just_jon]

Format the cell as Text prior to data entry.

 

[Mister H]

Hi:

That works when I try it on a new sheet but it does not seem to work when I have formatting or code that is also being applied to that cell. If I format the column as text and then set the number format as:

000-000000-0000-000000-000000-0000-0000

The system then reverts back to converting my number to a formula? Maybe I am missing someting...

Thanks,
Mark

 

[just_jon]

When you apply a numeric format, yeah, I think you are corecing the cell back to number.

Could use change event code to enter the ' and format with dashes, but that seems a bit overkill ...

 

[Mister H]

I am uncertain on how to accomplish that as I do not write much code? I am still open to suggestions from the world... THANKS for your time.

 

[just_jon]

Is your data always supposed to be the same length?

 

[Mister H]

Hi:

Yes the data will always be the same length. However, I want the use to be able to stop entering when the remainder of the number is 0's

For example if the user enter 1112222223333444444 I want them to tab out of the cell and then it populates with:

111-222222-3333-444444-000000-0000-0000

As mentioned the formatting and the lenght of the number are constant. Hopefully this makes sense. If not let me know.

THANKS Again,
Mark

 

[just_jon]

This seems like it may work, but the range in question must first be formatted as Text.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#00007F">Dim</SPAN> Rng <SPAN style="color:#00007F">As</SPAN> Range, TempStr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, c <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Set</SPAN> Rng = [A1:D10] <SPAN style="color:#007F00">' Change A1:D10 to the range you want to monitor</SPAN>
<SPAN style="color:#00007F">If</SPAN> Intersect(Target, Rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">Const</SPAN> NUM_LEN = 33 <SPAN style="color:#007F00">' this is the length of your value, less the dashes</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">Set</SPAN> Rng = Intersect(Target, Rng)
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Rng
    <SPAN style="color:#00007F">With</SPAN> Application.WorksheetFunction
        TempStr = c.Value & .Rept("0", .Max(NUM_LEN - Len(c.Value), 0))
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    c.Value = Left$(TempStr, 3) & "-" & Mid$(TempStr, 4, 5) & "-" & Mid$(TempStr, 10, 4) _
                  & "-" & Mid$(TempStr, 14, 6) & "-" & Mid$(TempStr, 20, 6) & "-" & Mid$(TempStr, 26, 4) _
                  & "-" & Right$(TempStr, 4)
<SPAN style="color:#00007F">Next</SPAN> c
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">Set</SPAN> Rng = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[Mister H]

Hi:

I am trying to work with this code. I have a couple of questions. How do I add a second Range to this code? I have 2 Ranges to use this coding on.

Also, when I use this code it seems to work however, when I delete the number out of the cell it puts in

000-00000-0000-000000-000000-0000-0000 (it will not allow me to remove the 0's which poses a problem when I use my Delete Rows Macro).

Can this code be altered to leave the cell blank when it is:

000-00000-0000-000000-000000-0000-0000

THANKS for your assistance and time,
Mark

 

[just_jon]

Hi:

I am trying to work with this code. I have a couple of questions. How do I add a second Range to this code? I have 2 Ranges to use this coding on.

Also, when I use this code it seems to work however, when I delete the number out of the cell it puts in

000-00000-0000-000000-000000-0000-0000 (it will not allow me to remove the 0's which poses a problem when I use my Delete Rows Macro).

Can this code be altered to leave the cell blank when it is:

000-00000-0000-000000-000000-0000-0000

THANKS for your assistance and time,
Mark

You *really* need to stay on a single post per topic.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#00007F">Dim</SPAN> Rng <SPAN style="color:#00007F">As</SPAN> Range, TempStr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, c <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#00007F">Set</SPAN> Rng = Union([A1:D10], [X1:Z10]) <SPAN style="color:#007F00">' Change A1:D10 to the range you want to monitor</SPAN>
<SPAN style="color:#00007F">If</SPAN> Intersect(Target, Rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">Const</SPAN> NUM_LEN = 33 <SPAN style="color:#007F00">' this is the length of your value, less the dashes</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">Set</SPAN> Rng = Intersect(Target, Rng)
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Rng
    <SPAN style="color:#00007F">If</SPAN> Len(c.Value) > 0 <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">With</SPAN> Application.WorksheetFunction
            TempStr = c.Value & .Rept("0", .Max(NUM_LEN - Len(c.Value), 0))
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        c.Value = Left$(TempStr, 3) & "-" & Mid$(TempStr, 4, 5) & "-" & Mid$(TempStr, 10, 4) _
                      & "-" & Mid$(TempStr, 14, 6) & "-" & Mid$(TempStr, 20, 6) & "-" & Mid$(TempStr, 26, 4) _
                      & "-" & Right$(TempStr, 4)
    <SPAN style="color:#00007F">Else</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> c
Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">Set</SPAN> Rng = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>


</FONT>