Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default Count number of digits in a cell

I have a column of numbers that all need to be 14 digits long. So I need to
add leading zeros... Currently I am doing =00&A4 and that would be great if
they were all 12 digits (12 + 00 = 14 digits) but some are 7, 8, 9, 10, and
11 digits. Is there a formula to count the number of digits in the cell?
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Count number of digits in a cell

Yes, there is a formula to count the number of digits in a cell. You can use the
Code:
LEN
function to count the number of characters in a cell, and then subtract any non-digit characters (such as spaces or dashes) to get the number of digits.

Here's an example formula you can use:
  1. Code:
    =14-LEN(A4)+LEN(SUBSTITUTE(A4," ",""))-LEN(SUBSTITUTE(A4,"-",""))

This formula assumes that the cell you want to count the digits in is A4. It subtracts the length of the cell from 14 (the desired length), and then subtracts the length of any spaces or dashes in the cell. This gives you the number of digits you need to add as leading zeros.

To use this formula, simply copy and paste it into the cell where you want to add the leading zeros. Make sure to replace "A4" with the cell reference for the cell you want to count the digits in.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,440
Default Count number of digits in a cell

=REPT("0",14-LEN(A1))&A1

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Julieeeee" wrote in message ...
|I have a column of numbers that all need to be 14 digits long. So I need to
| add leading zeros... Currently I am doing =00&A4 and that would be great if
| they were all 12 digits (12 + 00 = 14 digits) but some are 7, 8, 9, 10, and
| 11 digits. Is there a formula to count the number of digits in the cell?


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,311
Default Count number of digits in a cell

This returns the number of characters, including spaces, in A1.
=LEN(A1)

This will add the appropriate number of leading zeros.
=REPT(0,14-LEN(A1))&A1

HTH,
Paul

"Julieeeee" wrote in message
...
I have a column of numbers that all need to be 14 digits long. So I need
to
add leading zeros... Currently I am doing =00&A4 and that would be great
if
they were all 12 digits (12 + 00 = 14 digits) but some are 7, 8, 9, 10,
and
11 digits. Is there a formula to count the number of digits in the cell?



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default Count number of digits in a cell

Format your cells as 00000000000000

"Julieeeee" wrote:

I have a column of numbers that all need to be 14 digits long. So I need to
add leading zeros... Currently I am doing =00&A4 and that would be great if
they were all 12 digits (12 + 00 = 14 digits) but some are 7, 8, 9, 10, and
11 digits. Is there a formula to count the number of digits in the cell?



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default Count number of digits in a cell

That works like a dream. Thanks!!

"Niek Otten" wrote:

=REPT("0",14-LEN(A1))&A1

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Julieeeee" wrote in message ...
|I have a column of numbers that all need to be 14 digits long. So I need to
| add leading zeros... Currently I am doing =00&A4 and that would be great if
| they were all 12 digits (12 + 00 = 14 digits) but some are 7, 8, 9, 10, and
| 11 digits. Is there a formula to count the number of digits in the cell?



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 10
Default Count number of digits in a cell

That worked great. Thanks!

"PCLIVE" wrote:

This returns the number of characters, including spaces, in A1.
=LEN(A1)

This will add the appropriate number of leading zeros.
=REPT(0,14-LEN(A1))&A1

HTH,
Paul

"Julieeeee" wrote in message
...
I have a column of numbers that all need to be 14 digits long. So I need
to
add leading zeros... Currently I am doing =00&A4 and that would be great
if
they were all 12 digits (12 + 00 = 14 digits) but some are 7, 8, 9, 10,
and
11 digits. Is there a formula to count the number of digits in the cell?




  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default Count number of digits in a cell

One mo

=RIGHT(REPT("0",14)&A1,14)



Julieeeee wrote:

I have a column of numbers that all need to be 14 digits long. So I need to
add leading zeros... Currently I am doing =00&A4 and that would be great if
they were all 12 digits (12 + 00 = 14 digits) but some are 7, 8, 9, 10, and
11 digits. Is there a formula to count the number of digits in the cell?


--

Dave Peterson
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
New numbers Larry Excel Worksheet Functions 7 August 31st 06 04:54 PM
count a number range and a letter in a cell santaviga Excel Worksheet Functions 3 April 28th 06 11:11 AM
count cell if value present in every other cell + criteria lrbest4x4xfar Excel Worksheet Functions 2 September 26th 05 01:30 AM
Count number of values in a cell nyc_doc Excel Worksheet Functions 3 July 31st 05 12:34 AM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM


All times are GMT +1. The time now is 04:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"