Using Formatted Numbers

[inwell]

Hi,
I have made a column Reg Id. which is formatted to 4 Digit No. (when i enter 1 it takes 0001 and 333 it takes 0333)

but when i select any cell or copy values to diff columns,it copies just the no.i entered and not the whole 4digit no.(while coping it will copy 1 or 333 )
what should i do to get this 4 digit no. to use in any other sheet/mail merge ?

(in mail merge,the reg id field is included where i wish to print 4digit no. but it will take the nos and omit the prefix zeros )

[Pepe Le Mokko]

Try Copy - Paste special - Number formatting

[inwell]

As you can notice in the PIC attached,in formula BAR it show only no. and and not the 4 digit no.
What should i do so that i will get that 4 digit no.

[MrShorty]

In a case like this, what is shown in the formula bar is the cell's value and not what the cell displays. Sometimes this is a problem when using formatting to get the cell to "display" what you want if it is something different from the cell's value.

If you want the cell's value to be 0044, you will probably need to enter it as a text string by leading it with an apostrophe '0044
Or you can use the TEXT() function in another cell to convert the number 44 to the text string '0044 http://office.microsoft.com/en-us/ex...in=HA102752839

Recognize that changing a number to text like this changes other things, such as sort order, comparisons ("0044" is not equal to 44), and so on.

[AlKey]

Use something like this

=TEXT(ROW($A1),"0000")

[inwell]

Use something like this

=TEXT(ROW($A1),"0000")

Hi, where should i enter this string ? in cell or data validation..?
I forget to mention,i already have data validation rule made for this column which prevents me from entering Duplicate values.
And the column cell are formatted as CUSTOM and type "0000" so that if i enter 2 it takes 0002

[:) Sixthsense :)]

Assume that in column-A you have the values which is showing as Four Digit Numbers.

When you enter 2 in A2 cell it will display as 0002 because of the Custom Formatting (0000) applied for Column-A cells.

Just insert a new column in Column-B

In A2 Cell
2
But it will show as 0002 in the cell because of the custom formatting

In B2 Cell enter the below formula
=TEXT(A2,"0000")

Drag it down...

Refer the Column-B data in your Mailmerge instead of Column-A field.

[inwell]

ok

So as per your suggestion,i should create new column .
Is not possible by any trick to avoid this extra column and use the original column as source?

[:) Sixthsense :)]

Refer MrShorty suggestion in Post #4

[inwell]

nopes....

i will stick to your idea...creating new column..

btw..whenever i select any cell with value in this new column,i do not see the value in formula bar,but i see formula there..why?

[:) Sixthsense :)]

Yes... Because cells containing formula's will show the result of the formula in cell and the formula in formula bar.

If you want to convert the formula cells as values, then just select the range of formula cells and press Ctrl+C to copy and press Alt+E+S+V and give Ok which will convert the result of formula cells to values.