• Status: Solved
• Priority: Medium
• Security: Public
• Views: 391

# Excel - count number in cell, based on count, add number at start of cell.

I need a VB macro that counts the number of numbers in a cell A1 and if the count is 7, to update the cell with a given number before it, in cell D1, in this example, the value '0.'

So if A1 = 1234567, then the count in B1 = LEN(A1), as 3. Then D1 = 01234567, as the updated value.
0
Osley
2 Solutions

Commented:
not following you... you lost me with the "the count in B1 = LEN(A1), as 3"
0

Commented:
I agree that the question is not clear.  Does it have to be VBA or can we just do this with a basic formula?  For example in D1 you could have:
=IF(LEN(A1)=7,"0" & A1, A1)

-Bear
0

Commented:
if you really want something in VBA this will give you the basis for what you need:

If Len(Cells(1, 1)) = 7 Then
Cells(1, 4).Value = "0" & Cells(1, 1).Value
End If
End Sub

-Bear
0

Commented:
Are you looking to pad with zeros so there are 8 digits? If so, why not use custom format:
0000000#
No VBA necessary for this approach.

Or you can convert the number to text with a formula:
=TEXT(A1,"0000000#")
0

Author Commented:
Thanks for the 'if' function. Worked well.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.