Unwanted spaces

[LeisaA]

In a column, for 3400 rows, How do I get rid of an unwanted space preceding
the sentence and make sure the first letter of the sentence is capitalized.

 

[Paul C]

This formula should do the trick.

=UPPER(LEFT(TRIM(A1)))&RIGHT(TRIM(A1),LEN(TRIM(A1))-1)

enter it in a unused column, Copy down and then Copy-Paste Special Values
back to column A

 

[LeisaA]

THANK YOU SO MUCH! Have a great day!

This formula should do the trick.

=UPPER(LEFT(TRIM(A1)))&RIGHT(TRIM(A1),LEN(TRIM(A1))-1)

enter it in a unused column, Copy down and then Copy-Paste Special Values
back to column A

 

[Rick Rothstein]

Assuming what you asked is all that you want to do, then give this macro a
try...

Sub FixSentences()
Dim X As Long, LastRow As Long, CellText As String
Const FirstRow As Long = 2
Const DataColumn As String = "A"
LastRow = Cells(Rows.Count, DataColumn).End(xlUp).Row
For X = FirstRow To LastRow
CellText = Cells(X, DataColumn).Value
CellText = LTrim(CellText)
Mid(CellText, 1, 1) = UCase(Left(CellText, 1))
Cells(X, DataColumn).Value = CellText
Next
End Sub

 

[Jef Gorbach]

In a column, for 3400 rows, How do I get rid of an unwanted space preceding
the sentence and make sure the first letter of the sentence is capitalized.

Sub test()
For Each c In Range("A2:A3400")
c.Value = Trim(c.Value)
c.Value = UCase(Left(c.Value, 1)) & Right(c.Value, Len(c.Value) -
1)
Next
End Sub