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

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

--
Don Guillett
SalesAid Software

"Doug929" wrote in message
...
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.



Don Guillett wrote:
maybe trim first and then use mid
=mid(trim(activecell),2,9)

--
Don Guillett
SalesAid Software

"Doug929" wrote in message
...
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

Hi,

Perhaps you should use =CLEAN(xxx) to remove all non-printabl
characters. My gut feeling is that your text is coming from html an
for any reason has characters with ASCII code <= 32 in it, i.e. no
spaces

--
Stephane Rodrigue

-----------------------------------------------------------------------
Stephane Rodriguez's Profile: http://www.hightechtalks.com/m33
View this thread: http://www.hightechtalks.com/t229232

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

wrote in message
ups.com...
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.



Don Guillett wrote:
maybe trim first and then use mid
=mid(trim(activecell),2,9)

--
Don Guillett
SalesAid Software

"Doug929" wrote in message
...
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

Ed wrote:
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?


wrote in message
ups.com...
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.



Don Guillett wrote:
maybe trim first and then use mid
=mid(trim(activecell),2,9)

--
Don Guillett
SalesAid Software

"Doug929" wrote in message
...
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

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

--


Gary


"Doug929" wrote in message
...
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

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" wrote:

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

--


Gary


"Doug929" wrote in message
...
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

david's the "winner", not me

--


Gary


"Doug929" wrote in message
...
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" wrote:

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

--


Gary


"Doug929" wrote in message
...
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