select case macro error debug

Hi,
I have this code which is not running the possible reason which can be is contents of column 'S' has two spaces after which I am not able to remove,pl advice

Please Login or Register  to view this content.

This might help

Please Login or Register  to view this content.

@ Alf you get 6 of the strap for suggesting using select ...

@ arnab0711
There seems to be two problems that I can see

1/. You begin the code with a With Statement, then don't refer to it, you have "missed the point" so to speak!

2/. The Data in Column S is not e.g. IN it is actually IN & Char(160)&Char(160), I suspect this because the data is from the web or similar

If you check in the intermediate pane

Please Login or Register  to view this content.

this will return 4, not 2 as expected.
Check

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

Both return 160

In this case the Left statement I have used solves the problem, (all cases are 2 characters long)

Better to clean the original data with a Substitute formula in your workbook

=SUBSTITUTE(S1,CHAR(160),"")

or maybe find and replace Char(160) with ""

Try this

Please Login or Register  to view this content.

Hope this helps

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save