How can I insert a space before the last character in a range of cells

  • Thread starter Thread starter Jeff
  • Start date Start date
Sub Addspace()
Dim cell as Range
for each cell in selection
if not cell.hasformula then _
cell.value = left(cell.value,len(cell.value)-1) & " " & Right(Cell.Value,1)
Next
End Sub
 
Hi Jeff,
Assume these are for text and not numbers.
SpecialCells is an automatic limitation to the used range,
in addition limited to text constants in the following.
(untested code)

Sub Jeff_space()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim Cell as Range
On Error Resume Next 'In case no cells in selection
For Each Cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
if length(cell.value) > 0 then
cell.value = left(cell.value,length(cell.value)) & " " & right(cell.value)
end if
Next
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

More information on the extra coding in
http://www.mvps.org/dmcritchie/excel/proper.htm
 
Correction: Should have tested, my code line should have been more like Tom's,
rest of macro improves performance.

cell.value = left(cell.value,len(cell.value)-1) & " " & right(cell.value)
 
Wow, that was a fast response, thanks very much. That
works lovely except on completion it throws up an error
and starts debug with ref to the last line of code. run
time error 5 invalid procedure call or argument.

thid is my code -

Private Sub CommandButton1_Click()
Range("e2:e2000").Select
Application.CutCopyMode = False
Selection.Copy
Range("f2:f2000").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("F2:F2000").Select
Selection.Replace What:=" ", Replacement:="",
LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Dim cell As Range
For Each cell In Selection
If Not cell.HasFormula Then _
cell.Value = Left(cell.Value, Len(cell.Value) - 1) & " " &
Right(cell.Value, 1)
Next
End Sub
 
Looks like a problem with word wrap in the email:

cell.Value = Left(cell.Value, Len(cell.Value) - 1) & " " &
Right(cell.Value, 1)

should all be one line of code or you can put in a line continuation
charater (space and underscore)

cell.Value = Left(cell.Value, Len(cell.Value) - 1) & _
" " & Right(cell.Value, 1)
 
sorry thats not it, because The line was only wrapped i
the reply i gave. Maybe i could figure it out if i
understood the code you gave me (it would be great if you
could you comment it). when does the next end?. Ideally i
would like this to operate on a variable number of rows in
one column. At the moment i have set a range 2-2000 until
i figure out how to do it as a variable range. Maybe this
setting of the range is in conflict with the code i added
from you.

Thanks
 
I copied this out of your email and made it a sub:

Sub Addspace()
Dim cell As Range

For Each cell In Selection
If Not cell.HasFormula Then _
cell.Value = Left(cell.Value, Len(cell.Value) - 1) _
& " " & Right(cell.Value, 1)
Next
End Sub

It ran fine for me.

I added a check so it only tries to put a space in if there are two
characters in the cell.

Sub Addspace()
Dim cell As Range

' loop over the cells in the selection
For Each cell In Selection
' if the cell has a formula, skip it
If Not cell.HasFormula Then
if len(cell.Value) > 1 then
' take the left characters for the len of the string minus 1,
' concatenate a space to that, take the right single character and
' concatenate it to the end of the space
cell.Value = Left(cell.Value, Len(cell.Value) - 1) _
& " " & Right(cell.Value, 1)
End if
Next
End Sub
 
Back
Top