PDA

View Full Version : Solved: removing spaces



vzachin
07-20-2007, 07:36 AM
hi,

i have a cell that contains a string of about 1044 characters mixed with spaces. i want to remove these spaces but am running into a problem with an error message "FORMULA IS TOO LONG" when i do it manually.
i then tried the following but it doesn't work either.

Range("B3").Select
Cells.Replace What:=" ", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

as well as

Range("B3").Replace " ", ""


this will work only if there are less than 1044 characters, but i don't know what the threshold is.

is there a way to remove these spaces?


thank
zach

mikerickson
07-20-2007, 07:45 AM
Use the Replace function rather than the Replace method.


Range("B3") = Replace(Range("b3").Value, " ", vbNullString)


Ifyou are using before Excel 2003, the following code


Range("B3") = Application.Substitute(Range("b3").Value, " ", vbNullString)


might serve. Otherwise, a loop could be written.

Bob Phillips
07-20-2007, 08:01 AM
Ifyou are using before Excel 2003, the following code

Why?

vzachin
07-20-2007, 10:11 AM
hi mike,

very nice & easy.

thanks
zach

mikerickson
07-20-2007, 05:37 PM
Why?
I understand that Replace was introduced in 2003, previously one had to use the VB/Spreadsheet function SUBSTITUTE.

Bob Phillips
07-21-2007, 01:40 AM
I believe it appeared in Excel 2000, along with Split and Join. It is certainly in my verison of 2000, and whilst I haven't checked I am prettt sure it wqasn't in 97.