Hi Good afternoon to all of you from Sri Lanka,
I am trying to remove the above mentioned square which is around a Question Mark.
Please refer attachment for sample.
My version is office 2016.
Please help Thank you.Unicode issue.xlsx
Hi Good afternoon to all of you from Sri Lanka,
I am trying to remove the above mentioned square which is around a Question Mark.
Please refer attachment for sample.
My version is office 2016.
Please help Thank you.Unicode issue.xlsx
From here
The problem is that you are working with double byte or unicode text. The CODE function only works with ASCII (single byte) text so it is only picking up part of the two byte character.
The only way I can think of doing this is to use functions that handle unicode text and put a copy of the "white space" double byte character in a special cell so we can use it.
This formula works:
=SUBSTITUTE(A2,O$1,"")
assuming you have a copy of the "white space" character in O1.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Here's a UDF to remove the character
I made up this to determine the character which was copied into another cell.Function MyClean(Data) MyClean = Replace(Data, ChrW(&HE04C), "") End Function
Sub FindUnicode() For i = 1 To 128000 If InStr(1, ActiveCell, ChrW("&H" & Hex(i))) Then Exit For Next ActiveCell.Offset(, 1) = Hex(i) End Sub
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Thanks a lot for your response, in another forum, a user suggested this equation,
=IF(ISNUMBER(LEFT(C11)+0),C11,REPLACE(C11,1,1,""))
Now this works really well as far as the character I have mentioned is in the left hand side, problem is I sometime have the character in the right hand side end as well.
I have no clue how to use the VB which is mentioned above bro.
How do I run it?
MYClean is used like a normal function. It is added to a code module in the workbook. If you don't use VBA, you'll need to add the Developer tab to the ribbon to access it. You can rename it if you want.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
As you have posted this in another forum. please provide links in both forums to the other posts
Please read this
I've renamed your question to make it a more general application.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Wowwwww ! You are a genius aren't you?
Ok, I checked the attached worksheet and understood what you tried to achieve by the first micro,
What was the second part for? what exactly does it do and how do I use it in the same WORKBOOK?
Because, in the workbook which was attached, I found only the 1st part of your equation was mentioned in the module,
the part you said,
"I made up this to determine the character which was copied into another cell."
you haven't added that.
Why and what exactly does that coding do? Please apologize this Newbie
Last edited by New_Here; 04-16-2017 at 03:38 AM.
If you have a similar character, place it on its own in a cell and run FindUnicode. It should show the Hexadecimal for that character in the adjoining cell. That value can be used in the MyClean routine.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I came across a small issue, the myclean doesnt seem to work in all cells. Why is that? please refer attachment.
That says a E185 :/
If you could let me know how the 1st vba module needs to be re-adjusted that would be great, so that I will be able to look at other similar codes as well and apply the same.
Add characters to the array as required
Your workbook needs to be saved as an XLSM file to contain the macro coding.
Function MyClean(Data) Dim arr, a arr = Array(&HE185, &HE04C) For Each a In arr Data = Replace(Data, ChrW(a), "") Next a MyClean = Data End Function
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
If it's ALWAYS in the first position, something like this would work
=RIGHT(C11,LEN(C11)-1)
Otherwise you need something more complicated like Mac's
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
EXACTLY BROTHER ! EXACTLY .... This is what I needed, but due to time restrains I finally managed to solve the matter by creating multiple VBA codes i.e MyClear1,2,3 etc and filtered a single cell via colomns, so eventually the cell gets caught to one of the error codes.
Thanks a lot for your support and time.
I will update to this code once I have completed my current task.
Thank you again and I apologize if I had troubled you.
Another approach is instead of trying to exclude characters you don't want, is to instead only include the character you do want. Phone numbers lend themselves well to this.
So the cell formula would beFunction CleanContactNumber(ContactNumber, LegalCharacters As String) As String Dim Ch As String, CleanNumber As String Dim NumStr As String Dim I As Long NumStr = CStr(ContactNumber) CleanNumber = "" For I = 1 To Len(NumStr) Ch = Mid(NumStr, I, 1) If InStr(LegalCharacters, Ch) Then CleanNumber = CleanNumber & Ch End If Next I CleanContactNumber = CleanNumber End Function
=CleanContactNumber(C11,"0123456789-")
Where "0123456789-" are the legal characters.
Last edited by rlv; 04-16-2017 at 08:06 AM. Reason: typo in function
oops. Minor function error. I was in too much of a hurry. This line was erroneously inside the loop.
Corrected above and reposting the file here.CleanContactNumber = CleanNumber
Wow !!!
You guys seems to be having excel equations for Food.
So many methods to tackle one issue.
Thanks a lot brother for your kind support for this helpless Guy
But I will have to go with mdmackillops method because there are instances where these symbols seem to appear on addresses and many other character fields.
NEVERTHELESS your equation and effort will not go in vain, its saved for my future reference as I am a collector A Big Thank you for all you amazing people. Bye.