Unwanted spaces

  • Thread starter Thread starter LeisaA
  • Start date Start date
L

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.
 
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
 
THANK YOU SO MUCH! Have a great day!

Paul C said:
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
Click to expand...
 
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
 
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.
Click to expand...


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
 
Back
Top