How to read value from a cell with custom format mask

venkyv1255

New Member
Joined
Sep 12, 2012
Messages
2
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)
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Forum statistics

Threads
1,226,693
Messages
6,192,471
Members
453,726
Latest member
JoeH57

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top