Excel put a space at the beginning of text

Question

Hello from Steve

Excel put a space at the beginning of text please using vba

I tried the below

Sub VBA_Space()

Dim iInput As Integer, Output As String

iInput = 1

Output = Space(iInput)

End Sub

Answer 1

Hello,

Are you trying to keep the leading space visible in the cell, or only in the formula bar?

Excel trims leading spaces in some cases, so set the cell format to Text before writing the value. Use VBA to force the value as text and preserve the space:

Sub AddLeadingSpaceForceText()

Dim c As Range

For Each c In Selection

    If Not IsEmpty(c) Then

        c.NumberFormat = "@"

        c.Value = " " & CStr(c.Value)

    End If

Next c

End Sub

This stores the value as text so Excel keeps the leading space instead of removing it.

Answer 2

To add a space at the beginning of text in Excel using VBA, concatenate a space (or the result of the Space function) with the existing cell value.

For example, to add one leading space to the active cell:

Sub AddLeadingSpaceToActiveCell()
    ActiveCell.Value = Space(1) & ActiveCell.Value
End Sub

To add one leading space to every cell in a selected range:

Sub AddLeadingSpaceToSelection()
    Dim c As Range
    For Each c In Selection
        If Not IsEmpty(c) Then
            c.Value = Space(1) & c.Value
        End If
    Next c
End Sub

Explanation of the Space function in this context:

Space(1)   'returns a string containing 1 space character
Space(5)   'returns a string containing 5 space characters

So to add 5 spaces at the beginning instead of 1, use:

c.Value = Space(5) & c.Value

The code originally tried only creates a string of spaces and stores it in a variable; it does not write anything back to a cell. The key is assigning the result to a worksheet cell.

---

References:

• Space function
• 6.1.2.11.1.33 Space
• 6.1.2.11.1.34 Space$
• Getting started with VBA in Office
• How to use variables in Excel sub-procedures in Visual Basic for Applications

Answer 3

You're welcome. If you need more assistance please let me know.