CODEWITHSUNDEEP
arraysvbaexcel
Pr0no
Pr0no

Reputation: 4099

How can I delete leading spaces from each Excel cell?

Assume the following Excel sheet:

  | A
--+-----
1 |foo bar
2 |  bar baz
3 |   baz
4 | bam

I need a way to automatically delete all leading spaces:

  | A
--+-----
1 |foo bar
2 |bar baz
3 |baz
4 |bam

I am unable to find an Excel function to do this. Can anyone help me out?

Upvotes: 0

Views: 3147

Answers (3)

brettdj
brettdj

Reputation: 55692

You can do this very quickly with the array approach below which avoids loops altogether

While the code effectively could be run in a single long line I have broken it out for clarity.

This will update the cells to the immediate right of the original range.

Sub UpDateIt()
    Dim rng1 As Range
    Dim strText As String
    strText = ","
    Set rng1 = Range([a1], Cells(Rows.Count, "A").End(xlUp))
    With Application
        x = Replace(Replace(.Trim(Join(.Transpose(rng1), strText)), ", ", ","), Chr(32), Chr(10))
        rng1.Offset(0, 1) = .Transpose(Split(x, strText))
    End With
End Sub

Upvotes: 1

JustinJDavies
JustinJDavies

Reputation: 2693

Use =Trim(A1) to remove whitespce from either side of a string.

To replace the remaining whitespace character with a linebreak, you can use =SUBSTITUTE(A1," ",[X]) where [X] is one of:

  • Char(10) {for a line break}
  • Char(13) {for a carriage return}
  • [type] Alt+Enter to manually insert a line break character into an Excel cell

Help reference

TRIM(text) Removes all spaces from text except for single spaces between words.

SUBSTITUTE(text, old_text, new_text, [instance_num]) Substitutes new_text for old_text in a text string.

Upvotes: 2

Taliesin
Taliesin

Reputation: 414

If you want the cells to hold the converted values as well as the [return] for spaces in between words then use the following code:

'DECLARE VARIABLES
Dim myRng As Range, c As Range

'INITIALIZE VARIABLES
Set myRng = Range("A1:A4")

'LOOP THRU RANGE
For Each c In myRng.Cells
    'TRIM LEADING AND TRAILING SPACES
    c.Value = LTrim(c.Value)
    c.Value = RTrim(c.Value)
Next c

'ADD RETURN SPACE
myRng.Replace What:=" ", Replacement:=Chr(10), LookAt:=xlPart

Upvotes: 2

Related Questions