How to remove spaces in a cell

[lovebaby]

I have data as such - (space)number(space)

How do I remove the spaces via a formula or macro?

I have 40,000 entries so I would rather not do it manually.

thanks

 

[Gary''s Student]

If A1 has leading or trailing blanks then use:

=TRIM(A1)*1

If you have a column of these things then copy the formula down the column.

 

[Don Guillett]

from help index for replace
Sub replaceem()
Worksheets("Sheet7").Columns("f").Replace _
What:=" ", Replacement:="", _
SearchOrder:=xlByColumns
End Sub

 

[lovebaby]

For some reason, Trim does not work!

If A1 has leading or trailing blanks then use:

=TRIM(A1)*1

If you have a column of these things then copy the formula down the
column.

 

[lovebaby]

Didn't work. They're probably not sapces, but Character 160, like someone
else posted.

 

[Ken Wright]

Use Dave McRitchies Trimall Macro and run it against your data

Sub TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace what:=Chr(160), Replacement:=Chr(32), _
lookat:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants,
xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Jerry W. Lewis]

From the previous thread, the OP tried approaches that should have
worked unless there are CHAR(160) characters, in which case neither TRIM
nor multiplication will work.

For a helper column approach,
=VALUE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),""),"$",""))
would deal with both the CHAR(160) and the original question of the text
"$" characters.

To do it in place, use Data|Text to Columns, check the Delimited, check
the Other checkbox under and in the entry box to the right of it enter
Alt-0160 (hold down an [Alt] key while pressing 0160 in sequence on the
numeric keypad, not the numbers across the top of the keyboard). This
approach can change the "$" to part of the numeric format if it has not
already been removed.

Jerry

 

[Ken Wright]

Damn wordwrap

I'd point you off to Dave's site but it appears to be unreachable at the
moment (for me anyway)

The lines that read

lookat:=xlPart, SearchOrder:=xlByRows,
MatchCase:=False

should be one line only and read

lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

and

Selection.SpecialCells(xlConstants,
xlTextValues))

should read

Selection.SpecialCells(xlConstants, xlTextValues))

Regards
Ken......................

 

[lovebaby]

That worked !!!!!
Thanks

From the previous thread, the OP tried approaches that should have worked
unless there are CHAR(160) characters, in which case neither TRIM nor
multiplication will work.

For a helper column approach,
=VALUE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),""),"$",""))
would deal with both the CHAR(160) and the original question of the text
"$" characters.

To do it in place, use Data|Text to Columns, check the Delimited, check
the Other checkbox under and in the entry box to the right of it enter
Alt-0160 (hold down an [Alt] key while pressing 0160 in sequence on the
numeric keypad, not the numbers across the top of the keyboard). This
approach can change the "$" to part of the numeric format if it has not
already been removed.

Jerry

If A1 has leading or trailing blanks then use:

=TRIM(A1)*1

If you have a column of these things then copy the formula down the
column.

 

[David McRitchie]

You might want to look at using a macro the next time you have
40000 items to change, or even just one.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

That worked !!!!!
Thanks

From the previous thread, the OP tried approaches that should have worked
unless there are CHAR(160) characters, in which case neither TRIM nor
multiplication will work.

For a helper column approach,
=VALUE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),""),"$",""))
would deal with both the CHAR(160) and the original question of the text
"$" characters.

To do it in place, use Data|Text to Columns, check the Delimited, check
the Other checkbox under and in the entry box to the right of it enter
Alt-0160 (hold down an [Alt] key while pressing 0160 in sequence on the
numeric keypad, not the numbers across the top of the keyboard). This
approach can change the "$" to part of the numeric format if it has not
already been removed.

Jerry

If A1 has leading or trailing blanks then use:

=TRIM(A1)*1

If you have a column of these things then copy the formula down the
column.

 

[Bob Phillips]

We suggested that in his other post David. Only so many times :-(

Bob

You might want to look at using a macro the next time you have
40000 items to change, or even just one.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

That worked !!!!!
Thanks

From the previous thread, the OP tried approaches that should have worked
unless there are CHAR(160) characters, in which case neither TRIM nor
multiplication will work.

For a helper column approach,
=VALUE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),""),"$",""))
would deal with both the CHAR(160) and the original question of the text
"$" characters.

To do it in place, use Data|Text to Columns, check the Delimited, check
the Other checkbox under and in the entry box to the right of it enter
Alt-0160 (hold down an [Alt] key while pressing 0160 in sequence on the
numeric keypad, not the numbers across the top of the keyboard). This
approach can change the "$" to part of the numeric format if it has not
already been removed.

Jerry

Gary''s Student wrote:

If A1 has leading or trailing blanks then use:

=TRIM(A1)*1

If you have a column of these things then copy the formula down the
column.

 

[David McRitchie]

Hi Ken,
Didn't realize there was another subthread and too late to stop
my other reply in the thread, but my page appears to be up for me.
Anyway the page has additional information concerning the macro
besides the macro itself.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

If you can't locate mvps.org perhaps you have a DNS (domain name server)
problem with your ISP, and you might want to add another one.
DNS, reassignment
http://www.mvps.org/dmcritchie/excel/dns.htm
Even though I am on Verizon, they don't default me to Verizon
so I added a Verizon DNS server, and one more. (See the LockerGnome article).

 

[Ken Wright]

Hi Dave - i can connect fine now so probably just a glitch somewhere.

Regards
ken..........

Hi Ken,
Didn't realize there was another subthread and too late to stop
my other reply in the thread, but my page appears to be up for me.
Anyway the page has additional information concerning the macro
besides the macro itself.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall

If you can't locate mvps.org perhaps you have a DNS (domain name
server)
problem with your ISP, and you might want to add another one.
DNS, reassignment
http://www.mvps.org/dmcritchie/excel/dns.htm
Even though I am on Verizon, they don't default me to Verizon
so I added a Verizon DNS server, and one more. (See the LockerGnome
article).
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

I'd point you off to Dave's site but it appears to be unreachable at the
moment (for me anyway)

 

[Jerry W. Lewis]

You're welcome; glad it helped.

Jerry