trim spaces in a cell

[Guest]

How do i trim empty spaces in a cell containing an invoice number but the
length of the invoice number in column B is of dynamic length?

For example,
Column B
123 4567-------->1234567
987 3------------>9873
5 55------------->555

Thanks.


Ringo Tan

 

[Nick Hodge]

Ringo

If there is only ever one space then the worksheet function below should
work

=REPLACE(A1,FIND(" ",A1,1),1,"")

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Don]

Hi Tan,

You can do this manually using the find/replace function
on the edit menu. Highlight the cells you want to change,
click Edit/Find....select "Replace"...type a space in the
top box and nothing in the bottom box..then "replace all"

HTH,

Don

 

[Eric van Uden]

One way:

=SUBSTITUTE(A1," ","")

Eric

 

[Don Guillett]

A sub to do all

Sub TRIMEM()
For Each C In Selection
C.Value = Application.Substitute(C, " ", "")
Next
End Sub