Removing extra spaces form an address book.

[Earl C]

Greetings,

I have an Excel 2013 address book that has extra spaces between first names, last names and middle initials all in 1 cell. Is there an easy way to remove all the spaces between these components?

Thanks

Earl

 

[Big Wade]

With a formula you can use:
=SUBSTITUTE(A1," ","")

With VBA:

 strName = Replace(strName," ","")

 

[Momentman]

Maybe you could give some examples, but am thinking more of the TRIM command rather than substitute

Excel 2010
	H	I	J
9	victor momoh	victor momoh	victormomoh
Sheet1

Column I - shows the result of trim while column J shows the result of using the substitute command

 

[hiker95]

Earl C,

Welcome to the MrExcel forum.

Are you using a PC or a Mac?

If would help if you could give us several examples of the names with leading/trailing/extra spaces.


If the range of names was A2:A4, you could try the following macro code. The macro will remove leading, and, trailing space characters, and, it there are more than one space characters together, it should leave only one.


Sample raw data:

Excel 2007
	A
1	Names
2	First , Last, I
3	First, Lastname , I
4	First, Last, I
5
Sheet1

After the macro:

Excel 2007
	A
1	Names
2	First , Last, I
3	First, Lastname , I
4	First, Last, I
5
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub RemoveExtraSpaceCharacters()
' hiker95, 07/22/2014, ME793437
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
With Range("A2:A" & lr)
  .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
End With
Columns(1).AutoFit
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the RemoveExtraSpaceCharacters macro.