Strange Spaces

[adeel1]

Hi All

there is strange spaces which is i am unbale to remove it in Col A see attached

Adeel

[HansV]

They are so-called En Spaces (spaces the width of the letter n). See En Space.
You can do the following:
- Enter the formula =UNICHAR(8194) in an empty cell.
- Select and copy that cell (Ctrl+C).
- Press Ctrl+H to activate the Replace dialog.
- Click in the 'Find what' box and paste (Ctrl+V).
- Leave the 'Replace with' box empty.
- If necessary, click 'Options >>' to display the extra settings.
- Make sure that 'Match entire cell contents' is NOT ticked.
- Click 'Replace All'.

[adeel1]

thnx its done the trick...


Adeel

[DocAElstein]

Hello on this beautiful Summer morning…_
_... talking of beautiful things….

This sort of replace / remove stuff sometimes lends itself nicely to a simple Evaluate Range one liner thing, as a VBA solution alternative

Select the offending range, and run this. It should do the same job of getting rid of all your ChrW(8194)

Code: Select all

Sub GetRidOfStrtangeSpaces()                                                                                               ' https://excel.tips.net/T003037_Removing_Spaces.html   https://excelribbon.tips.net/T010741_Removing_Spaces.html
 Let Selection.Value = Evaluate("If({1},SUBSTITUTE(" & Selection.Address & ", """ & ChrW(8194) & """, """"))")
End Sub

( For Excel 2016 + you can do away with the extra If({1},___________) bit, but does no harm to leave it in for backward compatibility)

Alan

_.________________________________________________________________________________________________________________________



P.S.
I left in a function in the uploaded file that tells you, in various ways, what you have in strings. It’s an old inefficient function I did ages ago, but it does the job.
If you run this on the virgin file…_

Code: Select all

 Sub WotStrangeSpaces()                                                          '     https://excelfox.com/forum/showthread.php/2302-quot-What%e2%80%99s-in-a-String-quot-VBA-break-down-Loop-through-character-contents-of-a-string?p=15524&viewfull=1#post15524
Dim Ws1 As Worksheet: Set Ws1 = ThisWorkbook.Worksheets.Item(1)
 Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Ws1.Range("A1").Value)              '     https://pastebin.com/eutzzxHv
 Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(Ws1.Range("A2").Value)
End Sub

_... then amongst the outputs is one in the Immediate window, like

Code: Select all

"25030" & ChrW(8194) & ChrW(8194) & ChrW(8194) & ChrW(8194) & ChrW(8194) & ChrW(8194) & " "
"5031" & ChrW(8194) & ChrW(8194) & ChrW(8194) & ChrW(8194)

If you then select range A1:A2, run Sub GetRidOfStrtangeSpaces() , then run Sub WotStrangeSpaces() again, then you will see

Code: Select all

"25030"
"5031"

( I am not quite sure why the last normal space, " " , in A1 seems to have gone as well. I may need to look at that again, or someone else might have an idea???)

[SpeakEasy]

>not quite sure why the last normal space, " " , in A1 seems to have gone

I know ... :-)

It is because

Selection = Evaluate("If({1},SUBSTITUTE(" & Selection.Address & ", """ & ChrW(8194) & """, """"))")

hides a teeny bit of coercion ...

Essentially, you end up with a number in Selection, not a string.

WtchaGot_Unic_NotMuchIfYaChoppedItOff then converts it back into a string to parse it, but of course any trailing spaces are now long gone ...

[DocAElstein]

Thanks
I am not sure that explains why the last normal space is removed, ( by my Range Evaluate one liner code line - that was my question - I probably did not make it clear. )?
I agree that If both the strange spaces and the normal space is removed, then a number might be returned.
I was wondering why the normal space was removed

My Range Evaluate one liner code line will return a variant type that may have a text or a number type in it, I have no problem understanding that.
( I am away from my Excel computer now till tomorrow so can't check exactly what is being returned). But whether it returns a string or number, that does not explain why the normal space was removed by my Range Evaluate one liner code line.

I agree that my WtchaGot_Unic_NotMuchIfYaChoppedItOff function will take a number, if it is given one, and do coercing converting or similar to parse around a string as most of the function is to do with string manipulation of one sort or another. That I understand.

I will perhaps need to experiment a bit more to get the point, later

( It is all very intersting, as playing around with, and understanding, and controlling, the different ways to make a space in text can be quite useful when posting things around things like forums and comment sections in blog sites. I have seen people manage to do formatating/ indenting where no formating/ indenting seemed possible..... )

_._____

One possible answer to my question is that the Excel Substitute function converts the request to look for the strange spaces to a request to look for spaces generally. That does not sound correct to me, but is a possibly answer to explain the results, and perhaps helps better explain my original question

[SpeakEasy]

>I am not sure that explains why the last normal space is removed, ( by my Range Evaluate one liner code line - that was my question - I probably did not make it clear. )?

It does. And yep, it was clear you were referring to your one-liner. Blame Evaluate; it can only return a numerical value

Try the following to see this

Msgbox TypeName(Evaluate("5 "))

And, since the result of Evaluate here is a number, there won't be any trailing spaces

But even if you do manage to pass a string such as "25030 " to Selection, Excel will coerce it to a number if it recognizes it as a number.

The reality is that, if you want a string instead, then don't use Evaluate. Use VBA's Replace instead, and specifically tell Excel to treat the result as text, e.g.:

Selection = "'" & Replace(Selection, ChrW(8194), "")

[adeel1]

Thank all for your value able contribution.

Adeel

[DocAElstein]

Thank all for your value able contribution. Adeel

@ adeel1 - You is welcome :)
_.______________________________________________________________________________________________________________
_.,______________________________________________________________________________________________________________




@ SpeakEasy

Ah, I see now, thanks. I missed the point. (If you haven’t noticed already you will notice that I am rather slow at getting the point usually, unfortunately :( ).
I would have got this one though in not much more than a year or two I think, as I have seen some other effects which may have helped me get it. Or maybe not. Possibly rather this has helped me solve those other puzzles I had.
In any case you have saved me some time, which is very helpful to me just now, so thanks for that extra clarification.
_.____________________________________

I can see now a couple of things along the lines of what you are saying, variations thereof, and a few other things along the way coming out of all this…..

_(i) I think possibly I might explain this, Evaluate("5 ") returning a number 5, slightly differently:
I will get the same result also if I do something like this

Code: Select all

 Evaluate("=     5        ")

I think possibly this is doing something similar to if I physically type exactly that text in a cell, including the = and the extra spaces. I think you see similar effects in coding when by typing you add some extra spaces in some places, and the compiler thing takes them out. ( I think when you write a formula in a cell , it is similar to you writing a code line which Excel then compiles / runs or similar, when it updates the worksheet – But that is just one of my Layman guesses).

_.______________

( A passing remark: regarding your … Blame Evaluate; it can only return a numerical value ….. Possibly I have missed the point again, but I do know, and have experienced, that the Evaluate that I typically use returns a Variant type, and inside that returned variable I have seen a lot different things and types, numbers, strings, arrays, ranges … etc. )
_.________________


_(ii) The important point you make about coercion I think I can see by doing something similar to (i) , but in a slightly different way:
_ (ii)(a)
Any of these variations, will, in fact return me a string in vTemp , of two characters, which are a single 5 followed by a single space.

Code: Select all

 Let vTemp = Evaluate("=""5 """)
Let vTemp = Evaluate("=    ""5 """)
Let vTemp = Evaluate("= ""5 ""                                                  ")

_(ii)(b)
However, if I assign that to a cell, then it appears that Excel coerces it into a number. This is exactly what you are saying here …. But even if you do manage to pass a string such as "25030 " to Selection, Excel will coerce it to a number if it recognizes it as a number ….
_._______________________

Code: Select all

Sub EvalExcelNumberCoerceWonks() ' https://eileenslounge.com/vi ewtopic.php?p=297610#p297610
Dim vTemp As Variant
Rem _(i)
 Let vTemp = Evaluate("=5 "): Debug.Print vTemp  '                                         '  A slight odity here  -  in the Immeadiate window and extra space is shown either side of the    5       strange?
Debug.Print TypeName(vTemp)  '  Double
 Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(vTemp)  '  This suggests that a single  5  is there, as does the watch window with watch set on vTemp
Stop  '  Watch :   : vTemp : 5 : Variant/Double : Modul1.EvalExcelNumberCoerceWonks
Debug.Print

Rem _(ii)(a)
  Let vTemp = Evaluate("=""5 """): Debug.Print vTemp  '   a single  5  is shown
Debug.Print TypeName(vTemp)  '  String
Stop ' Watch :   : vTemp : "5 " : Variant/String : Modul1.EvalExcelNumberCoerceWonks
Debug.Print

'   _(ii)(b)
 Let Selection.Value = vTemp  '   In the cell,  it looks like Excel has coerced it to a number
Stop
 Let Selection.Value = Evaluate("=""5 """): Let vTemp = Selection.Value: Debug.Print vTemp  '  A slight odity here  -  in the Immeadiate window and extra space is shown either side of the    5       strange?
Debug.Print TypeName(vTemp)  '  Double  - looks like Excel has coeced the text "5" to a number
 Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(vTemp)  '  This suggests that a single  5  is there, as does the watch window with watch set on vTemp
Stop ' Watch :   : vTemp : 5 : Variant/Double : Modul1.EvalExcelNumberCoerceWonks

End Sub

( There is a strange peculiarity that in my Immediate window I may sometimes see an extra space either side of the 5 on the occasions when everything else telly me I have a single character of number 5 ( https://i.postimg.cc/y8FSJ2xW/Immediate ... number.jpg ) )
_._______________________________________



Using VBA Replace function, like in your suggestion , Selection = "'" & Replace(Selection, ChrW(8194), "") , is perhaps a sensible way to do it. (It will need a loop, of course, for more than one cell. ( My one liner works for one or more cells – any rectangular range selection ) )

I can see a variation of using that ' thing, which will make my one liner not loose normal trailing spaces

Code: Select all

 Sub GetRidOfStrangeSpaces()
 Let Selection.Value = Evaluate("=""'"" & If({1},SUBSTITUTE(" & Selection.Address & ", """ & ChrW(8194) & """, """"))")
End Sub

( But I will take a guess that the OP might possibly actually want numbers returned, in the cells where there are numbers and spaces of any type, and he may just have overlooked that he had some normal spaces in his original data)

[SpeakEasy]

>I have seen a lot different things and types, numbers, strings, arrays, ranges

Yes, my fault for being too quick/brief in what I typed- what I should have said is that if Evaluate results in anything that can be possibly be interpreted as a number (excel, unsurprisingly, is biased towards numeric values), then a number is what you'll get.

>I can see a variation of using that ' thing
Yep, that should work

[DocAElstein]

Just so I don't get confused later ....
....... if Evaluate results in anything that can be possibly be interpreted as a number (excel, unsurprisingly, is biased towards numeric values), then a number is what you'll getExcel will likely coerce it to if you try to put that result from Evaluate into an Excel cell.

or

....... if Evaluate results in anything that can be possibly be interpreted as a number (excel, unsurprisingly, is biased towards numeric values), then a number is what you'll get in a Excel cell.
_.____________________________

..
>I can see a variation of using that ' thing
Yep, that should work

seems to, looking quickly, but I have not tested extensively

[SpeakEasy]

>Excel will likely coerce it to if you try to put that result from Evaluate into an Excel cell.
No, the coercion is an integral part of the Evaluate function. It returns a variant containing a double if the parser can recognise the expression being evaluated results in a numeric. It uses the same numeric recognition algorithm as the IsNumeric function

>Any of these variations, will, in fact return me a string in vTemp , of two characters, which are a single 5 followed by a single space.

Well of course. They are not numeric! The below will show you what the parser is seeing, and whether it considers it numeric or not

MsgBox ("=""5 """) & ":" & IsNumeric("=""5 """)
MsgBox ("= ""5 """) & ":" & IsNumeric("= ""5 """)
MsgBox ("= ""5 "" ") & ":" & IsNumeric("= ""5 "" ")

>if I assign that to a cell, then it appears that Excel coerces it into a number
When you enter a string into a cell, whether by typing it or via VBA, Excel Evaluates it. So what ends up in the cell in your examples is:

Evaluate(Evaluate("=""5 """))
Evaluate(Evaluate("= ""5 """))
Evaluate(Evaluate("= ""5 "" "))

[DocAElstein]

….if the parser can recognise the expression being evaluated results in a numeric. It uses the same numeric recognition algorithm as the IsNumeric function….. That’s an interesting insight, thanks. I thought that sort of info would be low level stuff that was a Microsoft trade secret, and that we are usually left to try and guess or figure out ourselves…

_.________________________________

…the coercion is an integral part of the Evaluate function. It returns a variant containing a double if the parser can recognise the expression being evaluated results in a numeric….

I am not sure if there is some contradiction here, or maybe one of us is getting mixed up a bit, most likely me

But even if you do manage to pass a string such as "25030 " to Selection, Excel will coerce it to a number if it recognizes it as a number.

>Excel will likely coerce it to if you try to put that result from Evaluate into an Excel cell.
No, the coercion is an integral part of the Evaluate function. ....


>if I assign that to a cell, then it appears that Excel coerces it into a number
When you enter a string into a cell, whether by typing it or via VBA, Excel Evaluates it. So what ends up in the cell in your examples is:
Evaluate(Evaluate("=""5 """)) "))

_.____

My guess is that when you put a string with an equal in front of it in a cell , and hit enter, then something similar goes on to when in VBA you put the same string inside the quotes in this thing
Evaluate(" ")
That all ties up with what one is told early on about what is the basic idea of what Evaluate is about – typically you are told something along the lines of, …doing in VBA similar stuff to what is done in a cell, - in simple Layman terms

But in this previous snippet from my last macro, …._

Code: Select all

 Rem _(ii)(a)
  Let vTemp = Evaluate("=""5 """): Debug.Print vTemp  '   Two characters are shown - a  5 and a space  is shown in the immediate window  ( of course you don't see the trailing space , but if you move around with the keyboard arrows you will notice that the trailing space is there )
Debug.Print TypeName(vTemp)  '  String
Stop ' Watch :   : vTemp : "5 " : Variant/String : Modul1.EvalExcelNumberCoerceWonks
Debug.Print

'   _(ii)(b)
 Let Selection.Value = vTemp  '   In the cell,  it looks like Excel has coerced it to a number
Stop
 Let vTemp = Selection.Value: Debug.Print vTemp  '  
Debug.Print TypeName(vTemp)  '  Double  - looks like Excel has coeced the text  "5 "  to a number   5

_..... I am , just for now, thinking still , ( and I fully except I could be wrong ) , that Excel is doing some coercing when I put my vTemp into a Excel Cell

I am not saying that what you are saying here with the extra Evaluate is wrong, at least not yet.:
(I am not disputing that Evaluate(Evaluate("= ""5 "" ")) = 5 , the result 5 here being a number– I get that result as well )

When you enter a string into a cell, whether by typing it or via VBA, Excel Evaluates it. So what ends up in the cell in your examples is:
Evaluate(Evaluate("=""5 """)

I am just saying that I am not buying it as the explanation for what’s going on, yet, that’s all.
I may need to wait until I have more time to sit on the computer and think about it. I might need a lot more time as it opens up a lot of undiscovered territory, cans of worms, etc., and can upset a lot of people.


( Using Evaluate in other ways can be dangerous. You can do things that are not possible if you have a certain high level of misunderstanding. ( no typo there )
http://www.eileenslounge.com/viewtopic. ... 01#p280301
https://excelfox.com/forum/showthread.p ... DF-is-used
http://www.eileenslounge.com/viewtopic. ... 01#p280301 )