It's a bad idea to use spaces to align text (word processing 101). Additionally, it is changing the data, and if I entered a=b into a cell and all of a sudden it became a=b, I would be upset.
It is impossible to align text for a non-proportional typeface by adding spaces. Only a "best attempt" that roughly aligns them will do.
This cannot be done using an Excel (or VBA) built-in function.
I, therefore, did the following for fun. Accept it or reject it.
The text width of a string has no built-in function in VBA. You can discover various attempts if you search online, however the following advice is the most straightforward: Place a label on a user form and format it with the desired font (for example, Times New Roman, 8pt, Non-Bold, Non-Italic) and AutoSize = True and. WordWrap is not true.
This label changes in size when any text is assigned to it, allowing you to see how wide it is. Since the label utilizes a few additional pixels to the left and right, this doesn't exactly return the text's width, but for our purposes, we can ignore that.
Now put the following code into a regular module:
Function GetTextWidth(s As String) As Double
Static f As UserForm1
If f Is Nothing Then Set f = New UserForm1
.Font.Name = "Times New Roman"
.Font.Size = 8
.Font.Italic = False
.Font.Bold = False
.AutoSize = True
.WordWrap = False
.Caption = s
GetTextWidth = .Width