How to read value from a cell with custom format mask

[venkyv1255]

HI,
I am facing below issue. Please help me. ( using Excel 2010)

I have 2 excel sheets.
1. In first excel sheet user enters any number and it has custom format mask of '000000000000000'.
This format mask is not fixed. It varies from cell to cell.

Ex : if user enters 1234567890 then it will get displayed as 000001234567890 due to the format mask.

2. Then user copies this value into 2nd excel sheet where again the value is displayed as 000001234567890 due to source formatting.

Now I want to find the length of the String in the field using macros. I used below method:
<dl class="codebox" style="margin-right: 0px; margin-left: 0px; padding: 3px; border: 1px solid rgb(201, 210, 216); color: rgb(0, 0, 0); font-family: 'Lucida Grande', 'Trebuchet MS', Verdana, Helvetica, Arial, sans-serif; line-height: 18.200000762939453px; "><dt style="margin: 0px 0px 3px; padding: 0px; text-transform: uppercase; border-bottom-width: 1px; border-bottom-style: solid; border-bottom-color: rgb(204, 204, 204); font-size: 0.8em; font-weight: bold; ">CODE: SELECT ALL</dt><dd style="margin: 0px; padding: 0px; "><code style="margin: 2px 0px; padding: 5px 0px 0px; overflow: auto; display: block; height: auto; max-height: 600px; font-size: 0.9em; font-family: Monaco, 'Andale Mono', 'Courier New', Courier, mono; line-height: 1.3em; color: rgb(46, 139, 87); "> len(Cells(row, 1).Value) </code></dd></dl>

I get length as 10 instead of 15.


How to get the length displayed using the format mask? i.e 15 in this case. ( the format mask is not standard)

 

[BobUmlas]

Try .Text instead of .Value

 

[venkyv1255]

Try .Text instead of .Value

Thank You , Bob .
It came very hand.