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.
OsleyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ltlbearand3Connect With a Mentor 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)

Open in new window


-Bear
0
 
jsdrayCommented:
not following you... you lost me with the "the count in B1 = LEN(A1), as 3"
0
 
ltlbearand3Connect With a Mentor Commented:
if you really want something in VBA this will give you the basis for what you need:

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

Open in new window


-Bear
0
 
byundtCommented:
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
 
OsleyAuthor Commented:
Thanks for the 'if' function. Worked well.
0
All Courses

From novice to tech pro — start learning today.