Remove Leading Spaces in a Text Cell

[paulfitz320]

Would anyone have some code to remove the LEADING SPACE from the second line, down to and including the bottom line, in this text cell? It's in column B and needs to run on all cells down to B1000. Thank you.

9 / 2 first foal dam, 1m/8.3f winner, half-sister to useful winner up to 1¼m Zest fairly useful performer won handicap at Wolverhampton (by ½ length from Outrace) in April gambled-on 2/1, also won 12-runner similar event there last time by neck from Beau Vintage, quickening to lead close home having conceded first run stays 8.5f acts on polytrack and tapeta.

 

[Bosquedeguate]

I do not see leading spaces in your example

Use the TRIM function
=TRIM( " xxxx " ) will output "xxxx" - eliminated leading and trailing spaces

 

[paulfitz320]

Thanks. Don't need a formula. Was looking for some code. In any event your formula will delete the leading space before the first character in the entire cell.
I need to delete each leading space on each line in the cell.

 

[paulfitz320]

Sorry, the initial post didn't paste very well. Each line in the cell has a leading space in front of it, that I wish to remove.

 

[paulfitz320]

Maybe this is clearer.

 

[Akuini]

Try this:

Sub paulfitz320()
Dim i As Long, va
With Range("B1", Cells(Rows.Count, "B").End(xlUp))
    va = .Cells.Value
    For i = 1 To UBound(va, 1)
        va(i, 1) = Trim(va(i, 1))
        va(i, 1) = Replace(va(i, 1), vbLf & " ", vbLf)
    Next
    .Cells = va
End With
End Sub

I'm assuming that the leading space is only a single space, if that is not the case then we need to amend the code.

 

[Rick Rothstein]

Assuming as @Akuini did that there is only a single leading space, would a simple manual method to remove those single spaces be acceptable? If so, select Column B, then press CTRL+H to bring up the "Replace" dialog box, type CTRL+J followed by a space (that is two keystrokes) into the "Find What" field (it will not look like anything happened when you type the CTRL+J, but trust me, something happened), then type just CTRL+J into the "Replace With" field, make sure the "Match Entire Cell Contents" checkbox is NOT checked, then click the "Replace All" and your leading spaces should all be gone.

 

[paulfitz320]

Perfect!
Thanks very much, Akuini.

 

[Rick Rothstein]

If you really need it as a macro (instead of the manual method I posted a moment ago), this simple one-liner will work (it is the coded version of my manual method)...

Sub RemoveLeadingSpaceInsideCell()
  Columns("B").Replace vbLf & " ", vbLf, xlPart
End Sub

 

[paulfitz320]

Many thanks, Rick.