I found that in some cells, though i enter string as "aaa", Excel will auto add in some special character in front. so when i check value of those cells in debugging screen, it shows "??aaa", because of this, i cannot do comparison, is there a way i can remove those special character before doing comparison? Because the length of string i want to compare is not fixed, so i may not be able to use Left, Right as well. Can anyone help me? Thanks
How to remove special character in cell before comparison?
-
-
-
Re: How to remove special character in cell before comparison?
It's a little difficult to tell from your description, but a couple of ideas come to mind. It may be that the InStr function could meet your need. It returns the starting position of your search string within a second string. If the string is not found, it returns 0, which means you can use the number it has returned as part of your test. For example, if you were looking to see if "aaa" were a match for "??aaa", you could do this:
Alternatively, you might use the Asc function to identify the code number of your special characters, within a Replace function that will remove them. e.g.:
-
Re: How to remove special character in cell before comparison?
Could you attach a workbook that has one of these odd cells? I suspect that something else is happening.
-
Re: How to remove special character in cell before comparison?
Thanks, All, here is the workbook, look at Cell (E2), it contains certain char that i couldnt do comparison.
-
Re: How to remove special character in cell before comparison?
I have luck with the following formula to clean out the non-printable characters:
=TRIM(SUBSTITUTE(E4,CHAR(160),CHAR(32)))
You would probably need a helper column to contain the cleaned up string against which you could do your comparison.
CF -
-
Re: How to remove special character in cell before comparison?
Quote from CurtFunk;516711I have luck with the following formula to clean out the non-printable characters:
=TRIM(SUBSTITUTE(E4,CHAR(160),CHAR(32)))
You would probably need a helper column to contain the cleaned up string against which you could do your comparison.
CF
That is amazing, Thanks greatly, -
Re: How to remove special character in cell before comparison?
sorry, i have another qn, when i write in vba code, it doesnt work as what i type in excel cell, it will change all the contents to #NAME?, do u know why? Thanks
-
Re: How to remove special character in cell before comparison?
Is it cannot change value of the cell itself based the cell's original value? is there a way to do so? Thanks
-
Re: How to remove special character in cell before comparison?
The first reason your formula is not working when you put it into one of the cells of the worksheet is that you've put your VBA variable "varCell" right into the middle of it. While your code may know what varCell is, the worksheet formula does not. But you're also correct that if you got a formula there that would do what you were trying to do, it would create a circular reference, and fail for that reason as well. Rather than put in a formula, why not just clean up the actual values of the cells, without bothering with a For-Next loop, or formulas or anything. It's as simple as:
-
Re: How to remove special character in cell before comparison?
That really helps, thanks so much,
-
-
Re: How to remove special character in cell before comparison?
btw, is there a way i can use rRange.formulaR1C1 to formulat all cell formulas in the range instead of looping through indiidual cells? eg. i want to do vlookup for all cells in the range, now i am using below, is there any other convenient way to do so? Thanks
CodeFor Each varCell In SecuName.Cells 'varCell.FormulaR1C1 = "=LOOKUP(RC[1],rangeBPlookupBbCode,rangeBPlookupSecuName)" varCell.FormulaR1C1 = "=INDEX(rangeBPlookupSecuName,MATCH(RC[1],rangeBPlookupBbCode,0))" If Application.WorksheetFunction.IsNA(varCell.Value) Then varCell.FormulaR1C1 = "=VLOOKUP(RC[1],rangeGS,4,FALSE)" End If Next varCell
-
Re: How to remove special character in cell before comparison?
sorry, after i use rRange.Replace Chr(160), vbNullString, xlPart, it changes cells in the range all to empty, can help?
-
Re: How to remove special character in cell before comparison?
ke9c,
Codes should be wrapped in code tags, and you have turned a blind eye even after 24 posts here. Please maintain decorum.
This thread would have been locked, had it not been for the responses from the volunteers.
-
Re: How to remove special character in cell before comparison?
hi
would you help me with removing any of the special characters in an excel sheet please....characters such as "."and "-".. thanks
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!