Excel query

nomorekids

Widen the column, in a nutshell, your column width wont show that many numbers, alternatively reduce the font size, then decrease decimal or format number to no decimal places. x

AnotherJoe

nomorekids wrote: »

Widen the column, in a nutshell, your column width wont show that many numbers, alternatively reduce the font size, then decrease decimal or format number to no decimal places. x

Noooooo! Its not a number. Its just a string of characters.

nomorekids

AnotherJoe wrote: »

Noooooo! Its not a number. Its just a string of characters.

Oh ok, then put a ' in front (single quote), it will format the cell as text instead of number and like I said before, widen the column. x

Tom99

If you want to work with the 17 digit number and still be able to see the correct result to as many digits as you like but in a text format you can.
Split the number into two, in the example below your original number is in C3. C4 is C3*2 but it ends in 600 whereas the correct result ends in 604.
Row 5 and 6 split the 17digit number into the left 10 digits and the right 7 digits both multiplied by 2.
Row 7 and 8 show these two sub-calculations as text and row 9 shows the end result by joining the two text's in row 7 & 8
Rather a faff I think and my simple example will not work for every calculation. It will not work for example if the manipulation you want to do on the 7 digits on the left results in an answer not also comprising 7 digits.
There would be a way round that by making the formulas more complicate but I think you get the gist of how you could do it.
Why you would want that level of accuracy it another matter but maybe you don't?

...COL.......B....................... C...................................... D
ROW
.2...Format Col C............Cacl.......................Formula in Col C
.3.....Text ............17099131256910302 ..... 17099131256910302
.4 ... Number.......34198262513820600...........=C3*2
.5 ... Number........... 341826250.................... =LEFT(C3,10)*2
.6 ... Number .......... 13820604...................... =RIGHT(C3,7)*2
.7.....Number............3419826250....................=TEXT(C5,0)

.8....Number.............13820604.......................=TEXT(C6,0)
.9.....Text.............34198262513820604...........=TEXT(C5,0)

Gers

Carrot007 wrote: »

So 100% no need to be a number and should be text.


Numbers are number so you can calculate on them. This is just a string whihc happens to be numerical and excel tries to double guess what you want and gets it wrong.

It's in as text. Have tried all available options.

JP1978 wrote: »

Adding ' before the number as above will fix it - just like when you try and enter a phone number 01782123456, excel will format it as 1782123456 - by using the ' before the 0 it will display as intended.

Tried that, thanks for the suggetion. Didn't work.

nomorekids wrote: »

Widen the column, in a nutshell, your column width wont show that many numbers, alternatively reduce the font size, then decrease decimal or format number to no decimal places. x

Column is more than wide enough. Other data, in the same format, works fine.

I'm going to make a new worksheet - may be a glinch that's not worth time trying to eradicate.

Thanks for all the suggestions, it's baffling.

Tom99

[FONT=Verdana, sans-serif]You are not formatting the cell as Text correctly. Here is a screen shot of the calculations I posted above and you can see that the cell C3 is formatted as text and shows exactly what you want it to:[/FONT]

[FONT=Verdana, sans-serif]https://ibb.co/PgHwzYw[/FONT]

[FONT=Verdana, sans-serif]Just tried again and had your problem, you need to format the cell before you enter the number. If you have already entered the number you do not get an ABC text option your only choice is 1.70991E+16[/FONT]

Gers

Tom99 wrote: »

[FONT=Verdana, sans-serif]You are not formatting the cell as Text correctly. Here is a screen shot of the calculations I posted above and you can see that the cell C3 is formatted as text and shows exactly what you want it to:[/FONT]

[FONT=Verdana, sans-serif]https://ibb.co/PgHwzYw[/FONT]

[FONT=Verdana, sans-serif]Just tried again and had your problem, you need to format the cell before you enter the number. If you have already entered the number you do not get an ABC text option your only choice is 1.70991E+16[/FONT]

That's perfect and worked - thanks. I'm only recording certificate numbers and not doing any calculations.

Heedtheadvice

A pity really you did not write what you were trying to do as that would have saved a lot of posts and effort to show the solution.
You could have just entered "cert number 017156788765334567889976543…" and it would have automatically shown up correctly! ! (Entering the correct digits of course....). In fact prefixing the digits with any alpha charecters.....

Gers

Heedtheadvice wrote: »

A pity really you did not write what you were trying to do as that would have saved a lot of posts and effort to show the solution.
You could have just entered "cert number 017156788765334567889976543…" and it would have automatically shown up correctly! ! (Entering the correct digits of course....). In fact prefixing the digits with any alpha charecters.....

I did write clearly about the problem. I have all the required information in columns to do without the need to write 'cert number' each time.

I'm grateful for all suggestions and, especially, the solution.

Tom99

Gers wrote: »

I did write clearly about the problem. I have all the required information in columns to do without the need to write 'cert number' each time.
I'm grateful for all suggestions and, especially, the solution.

I thought you might want to use the number in a calculation, maybe you are a time traveler? You question gave me a little mental exercise before breakfast - so thank you.