Trim Function... does not work?

[Guest]

I am using trim and can not figure out why it does not work!! I have some
text in a cell and use mid fuction to get a piece out, I want to trim the
spaces to use as worksheet name but can not figure why it won't trim spaces.
The data is a html I open in Excel, no problem, I save it as Excel and it
still does not work.

Dim strMarket As String
strMarket = Trim(Mid(ActiveCell.FormulaR1C1, 9))
ActiveSheet.Name = strMarket

 

[Don Guillett]

maybe trim first and then use mid
=mid(trim(activecell),2,9)

 

[samer.kurdi]

I have had this when pasting data which contained what looked to me
like spaces but were in fact another kind of special character.

Here's a possible solution:
Go to one of your cells, copy what looks like a space, then go to
find/replace and replace it with a normal space.

Otherwise you can write Left/Right/Mid formulas that look for the
character and process it to your liking.

 

[Ed]

Sometimes copy/paste doesn't get it. If so, select the character. Then do
something like
MsgBox Asc(Selection.Range.Text)
That will give you the Chr() code to use.

HTH
Ed

 

[Gary Keramidas]

david mcritchie has a macro that might show what's keeping the trim function
from working properly

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

 

[Guest]

And the winner is ..... Gary!

Thanks I found what I needed in the code you refered me to. I am on the cell
I am converting and run this then rename my sheet. Works like a champ. Let me
know if you think it is fine. Thanks again.


' Name Sheet. The cell looks like this 'Market: WASHINGTON DC '
' and I want the sheet name to be this. 'WASHINGTON DC'
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
strMarket = Trim(Mid(ActiveCell.FormulaR1C1, 9))
ActiveSheet.Name = strMarket

 

[Gary Keramidas]

david's the "winner", not me

--


Gary

And the winner is ..... Gary!

Thanks I found what I needed in the code you refered me to. I am on the
cell
I am converting and run this then rename my sheet. Works like a champ. Let
me
know if you think it is fine. Thanks again.


' Name Sheet. The cell looks like this 'Market: WASHINGTON DC '
' and I want the sheet name to be this. 'WASHINGTON DC'
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
strMarket = Trim(Mid(ActiveCell.FormulaR1C1, 9))
ActiveSheet.Name = strMarket

 

[davegb]

Sometimes copy/paste doesn't get it. If so, select the character. Then do
something like
MsgBox Asc(Selection.Range.Text)
That will give you the Chr() code to use.

HTH
Ed

I tried this with a weird character I have in some spreadsheets
downloaded from Crystal Reports, but it won't work. Maybe it's changed
since XL2000, but I can't run a macro with a character selected (edit
box open).
Can macros be run with the edit box open in newer versions?