[Macro help request] formatting the integers.

[misiek]

Hi all,

My first post here, I am a bit nervous.

First off, I consider myself as belonging to the pool of 99% Excel users who use 1% of its power.

Came across this forum the other day and it looked like the right place to ask questions.

Let me begin with the data structure:

ColumnA | ColumnB
AAA | 12345678
BBB | 90123456

I am trying to format the integer numbers in ColumnB in the following way:
If ColumnA reads AAA, add TRAILING zeros to the number in ColumnB of the same row until there are 14 digits there.
If ColumnA reads BBB, add LEADING zeros to the number in ColumnB until there are 10 digits there.

Could you guys/gals give me some examples on how this could be done?
Since there are potentially hundreds of rows that need to be formatted this way, doing this manually is a nightmare.

Thanks in advance

 

[Tom Urtis]

Can you please confirm your actual intention where the trailing zeros are requested for AAA in column A.

Adding that many zeros in your case would change 12+ million to a number that is 12+ trillion. Is that what you want, or do you only want the number to be formatted with all those zeros? If the latter, why are you doing that? It would be confusing for people looking at a number which looks like it's in the trillions but really only a fraction of that if they did not know better.

 

[jindon]

Hi,

try

=IF(A1="AAA",A1&B1&REPT(0,14-LEN(B1)),A1&TEXT(B1,"0000000000"))

hope this helps

jindon

 

[misiek]

Can you please confirm your actual intention where the trailing zeros are requested for AAA in column A.

Adding that many zeros in your case would change 12+ million to a number that is 12+ trillion. Is that what you want, or do you only want the number to be formatted with all those zeros? If the latter, why are you doing that? It would be confusing for people looking at a number which looks like it's in the trillions but really only a fraction of that if they did not know better.

Tom,

The goal is to add trailing / leading zeros to the existing numbers based upon the rule.

This is a part of a bigger problem I am trying to solve where the numbers in two columns on two different worksheets (Sheet1 and Sheet2) are to be compared and rows with duplicates are to be deleted from Sheet1 (got that part covered by a macro already).

Humans will seldom, if ever, look at the whole 14-digit numbers.

Sorry about confusion.

Thank you.

Jacek

 

[misiek]

Hi,

try

=IF(A1="AAA",A1&B1&REPT(0,14-LEN(B1)),A1&TEXT(B1,"0000000000"))

hope this helps

jindon

Jindon,

Thank you, I will try your suggestion today.

Jacek

 

[Tom Urtis]

Humans will seldom, if ever, look at the whole 14-digit numbers.

Keep in mind you're talking to Excel people, who look at and work with 14-digit numbers all the time. Then again, maybe that means we're not humans after all, at least not normal ones.

You wrote...
"I am trying to format the integer numbers in ColumnB"

...which prompted my question, especially regarding the "format" issue. Formatting only changes a value's appearance; that value's underlying true value is still in the cell. So, if you were going to add all those zeros for calculations later that involve column B, you should be aware of the possible consequences.

Anyway, if jindon's suggestion does it for you, that's the important thing.

 

[misiek]

Got a syntax error

This is the macro, I tried:

Sub format()
=IF(A1="AAA",A1&B1&REPT(0,14-LEN(B1)),A1&TEXT(B1,"0000000000"))
End Sub

Grrr...

What am I missing?

Thanks!

 

[jindon]

hi

It is not MACRO, but worksheet function

you can enter the formula in the available cell

if you prefer the macro then

Sub a()
Dim r As Range
For Each r In Range("a1", Range("a65536").End(xlUp))
    If Not IsEmpty(r) Then
        If r.Value = "AAA" Then
            r.Offset(, 2) = r.Value & r.Offset(, 1) & _
                WorksheetFunction.Rept(0, 14 - Len(r.Offset(, 1)))
        Else
            r.Offset(, 2) = r.Value & Format(r.Offset(, 1), "0000000000")
        End If
    End If
Next
End Sub

rgds,

BTW, I agree with Tom.

jindon

 

[misiek]

Jindon,

I tried your slightly modified formula:
=IF(A1="AAA",B1&REPT(0,14-LEN(B1)),TEXT(B1,"0000000000"))
and it worked!

Got the 14/10 digit strings as intended.


However, your macro generated somewhat strange output:
AAA-followed-by-14-digits or
BBB-followed-by-10-digits.

It's getting late, so I try to get a crack at it tomorrow.

Thanks, again

 

[jindon]

Hi

Hummm, now I understand what you are after.
You didn't need col.A letter to lead.

Sub a() 
Dim r As Range 
For Each r In Range("a1", Range("a65536").End(xlUp)) 
    If Not IsEmpty(r) Then 
        If r.Value = "AAA" Then 
            r.Offset(, 2) = r.Offset(, 1) & _ 
                WorksheetFunction.Rept(0, 14 - Len(r.Offset(, 1))) 
        Else 
            r.Offset(, 2) = Format(r.Offset(, 1), "0000000000") 
        End If 
    End If 
Next 
End Sub

should work

rgds,
jindon