# [Macro help request] formatting the integers.

#### misiek

##### New Member
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

### Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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.

Hi,

try

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

hope this helps

jindon

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

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

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.

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!

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

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

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

Replies
7
Views
768
Replies
1
Views
1K
Replies
0
Views
849
Replies
3
Views
2K
Replies
6
Views
1K

Threads
1,219,004
Messages
6,145,703
Members
450,635
Latest member
Rookie3510

### 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

### 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