Aligning numbers when combining numbers & text

I'm trying to get cells containing numbers and text to align in the column. This would be easy to solve with custom number formatting if I was only displaying numbers. For example, cell A1 contains the number 2 and cell A2 contains the number 56. Both cells are left-justified. With general number formatting, the number 2 in A1 aligns with the number 5 in A2. If I format the cells with a custom number format of "???" then the number 2 in A1 aligns with the number 6 in A2. In other words, the "ones", "tens", "hundreds" places in the column line up. However as soon as I add text after the number, the formatting reverts to normal, i.e. I no longer get the space in front of single-digit numbers. I tried using custom number format "???;;;@" but that didn't help. The only way I can get the numbers to align is to pad single digits with a leading "0". Is there a way to do what I'm trying to do? I have attached a .jpg file illustrating the problem if the above is not clear.

Hi

Why don't you format the cell alignment to be right aligned???

rylo

That would work if the text following the number was always the same, but in this case it will vary from cell to cell so the numbers won't be aligned. I need to left-align all cells in the column.

Use the custom format 0"gallons"

Hi

Perhaps an event macro. Right click on the sheet tab, select view code, and enter the code

Please Login or Register  to view this content.

Now type something into a cell in column A.

This is premised on the number being the first part of the string only.

rylo

The easy way would be to put the units in a different column.

Perhaps an event macro. Right click on the sheet tab, select view code, and enter the code


Code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errhand
Application.EnableEvents = False
If Target.Cells.Count = 1 And Target.Column = 1 Then
vall = Val(Target.Value)
strr = WorksheetFunction.Substitute(Target.Value, vall, "")
Target.Value = Format(vall, "000") & strr
End If
errhand:
Application.EnableEvents = True
End Sub
Now type something into a cell in column A.

This is premised on the number being the first part of the string only.

This works well, but is there a way to get rid of the zeroes so they don't show on the worksheet?

Hi

you could do something like

Please Login or Register  to view this content.

However, unless you have a font type that makes the space character the same width as a number (eg Courier, Terminal), then the numbers will not be aligned. Try it with say ariel, then convert the font to courier. Another option would be to replace " " with "___", but then you would have a leading underscore character.

rylo

you could do something like

Code:
Target.Value = Right(" " & vall, 3) & strr

This is perfect; many thanks. It forces me to use Courier New to get everything lined up properly. I'd prefer to use Arial, but hey, you can't have everything.