Trim Function... does not work?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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.
 
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
 
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
 
david's the "winner", not me

--


Gary


Doug929 said:
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
Click to expand...
 
Ed said:
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
Click to expand...

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?
 
Back
Top