[Macro help request] formatting the integers.

misiek

New Member
Joined
Jan 26, 2005
Messages
14
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. :biggrin:

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

:biggrin:
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi,

try

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

hope this helps

jindon
 

misiek

New Member
Joined
Jan 26, 2005
Messages
14
Tom Urtis said:
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

New Member
Joined
Jan 26, 2005
Messages
14

ADVERTISEMENT

jindon said:
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

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
misiek said:
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

New Member
Joined
Jan 26, 2005
Messages
14

ADVERTISEMENT

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... :oops:

What am I missing?

Thanks!
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
hi

It is not MACRO, but worksheet function

you can enter the formula in the available cell

if you prefer the macro then

Code:
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

New Member
Joined
Jan 26, 2005
Messages
14
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.
(y)

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 (y)
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi

Hummm, now I understand what you are after.
You didn't need col.A letter to lead.
Code:
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
 

Forum statistics

Threads
1,147,621
Messages
5,742,184
Members
423,710
Latest member
Duarte85

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
Top