Remove Space from Text String

isleofman

New Member
Joined
Jan 12, 2010
Messages
38
I have a text sting in column A which includes spaces. I want to remove the spaces and import the now reduced text string into column B.

How would I do that using VBA?

I don't want to use =SUBSTITUTE(A1," ","") as I wish to change the text string in column A and when I change the text sting the =SUBSTITUTE(A1," ","") is then required to be re-entered in column B every time.

I hope somebody understands what I'm saying here!
Many thanks.
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Sub test()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    With Range("A" & i)
        .Offset(, 1).Value = Replace(.Value, " ", "")
    End With
Next i
End Sub
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Code:
Sub test()
Range("A:A").Copy Range("B:B")
Range("B:B").Replace " ", ""
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,504
Messages
5,529,259
Members
409,859
Latest member
emperorgenghiskhan
Top