Formatting Numbers to 8 digits exactly for every cell.

[Noobllianz]

Hi Guys.

Could really use the help.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]item 1[/TD]
[TD]100000[/TD]
[TD]100[/TD]
[TD]10000000[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]item 2[/TD]
[TD]20000[/TD]
[TD]20[/TD]
[TD]20000000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]item 3[/TD]
[TD]30000[/TD]
[TD]3000[/TD]
[TD]30000000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]item 4[/TD]
[TD]4000[/TD]
[TD]400[/TD]
[TD]40000000[/TD]
[/TR]
</tbody>[/TABLE]


Basically, I will copy paste item prices from a sales sheet onto a blank sheet.

The maximum length of a number can only be 8 digits. Any numbers that are not 8 digits need a _ in front or back of the number if possible.

e.g , b1 = __100000 or 100000__
b2 = ___20000 or 20000___
c1 = _____100 or 100_____
c2 = ______20 or 20______

Is there any way to do this? If the numbers were consistant, i would use concatnate to add _ to the numbers, however it is unstructured.

Appreciate the help!


Thanks.

-b

 

[dreid1011]

If you want a formula, you could use something like this:

=B1&REPT("_",8-LEN(B1))

 

[Noobllianz]

SOrry for the late reply, I couldnt get to the computer all weekend.

BUT WOW, thanks for the reply, using this formula, it works excatly the way I was hoping!



I was wondering if the structure of the data will always be the same in terms of Column data.

Is there a way to use VBA to create a macro to change column b, c, and d and reformat the data i copy paste to that worksheet?

Thanks again for this help!

So basically a macro to apply the =B1&REPT("_",8-LEN(B1)) formula to b1 replace with the applied rate.

Thanks!

-b

 

[mumps]

Maybe:

Sub Test()
    Application.ScreenUpdating = False
    Dim LastRow As Long, rng As Range
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each rng In Range("B1:D" & LastRow)
        rng = WorksheetFunction.Rept("_", 8 - Len(rng)) & rng
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

Here is another macro for you to consider...

[table="width: 500"]
[tr]
	[td]Sub TestToo()
  With Intersect(Columns("B:D"), ActiveSheet.UsedRange)
    .Value = Evaluate(Replace("IF({1},REPT(""_"",8-LEN(@))&@)", "@", .Address))
  End With
End Sub[/td]
[/tr]
[/table]

 

[Noobllianz]

Here is another macro for you to consider...

[table="width: 500"]
[tr]
	[td]Sub TestToo()
  With Intersect(Columns("B:D"), ActiveSheet.UsedRange)
    .Value = Evaluate(Replace("IF({1},REPT(""_"",8-LEN(@))&@)", "@", .Address))
  End With
End Sub[/td]
[/tr]
[/table]

Thank you very much, it is exactly what I was after.

Apologies for the late response, I have been away

 

[Noobllianz]

Thank you!