Removing Japanese characters from cell

[Djbower1]

Hi all,

My first post here so please be nice.

Most the time I can find my answers by alot of gooogling, searching and guessing but this time I need some help.

I have a big spreadsheet which has random Japanese and English within a cell. I need to remove the Japanese from them and leave the English. I found the below code by StephenR which nearly works for me but it also remove forward slashes "/". Can this code be adapted to suit?

Thanks

Dan

Please Login or Register  to view this content.

[Leith Ross]

Hello Djbower1,

This will leave only ASCII values from 32 to 127.

Please Login or Register  to view this content.

[Loganeb]

Hello Djbower1, and welcome to the forum,

Could you provide a small sample? Something with private data removed? I'd like to experiment on this as well.

Edit: Leith's code works like magic

[Djbower1]

Thanks Leigh, It worked. Many thanks for the very fast response.

Loganeb, Sample below for reference.

ALPHARD / VELLFIRE アルファード / ヴェルファイア
ALION アリオン
ALTEZZA アルテッツァ
ALTEZZA GITA アルテッツァ ジータ


Not sure if I should start another thread for this but as I dont like clogging up forums.....

On the same spreadsheet I'm currently working on I have a rows which have duplicate values in columns like show below. In the example data below is there anyway to delete the cell if the value already exists within that row.

It would have to delete cells A4, A5 & A6 and shift cells left

Please Login or Register  to view this content.

Does this make sense?

Thank you.

Dan

[Djbower1]

Sorry to be a pain Leigh but is there any way to have you code work only on selected cells and not the whole sheet?

Thanks,

Dan

[Leith Ross]

Hello Dan,

もじろん。 Can you post a copy of your workbook? I can use it to test the macro is working correctly.

If your workbook contains any sensitive information, please redact that information before you post.

How To Post Your Workbook

1. At the bottom right of the Reply window, Click the button Go Advanced
2. At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
3. Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
4. Click the Select Files button at the bottom middle of the dialog.
5. In the new window Find the file you want to upload, select it, and Click Open.
6. You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
7. Wait until the file has completely uploaded before you exit the File Manager dialog.

[Djbower1]

I could not attach the main workbook as it is over 30MB

I have attached a extract.

Thanks,

DanExample.xlsx

[Leith Ross]

Hello Dan,

Thanks for the posting the workbook. Are these cells being manually selected on "Sheet1" ?

[Djbower1]

Hello Dan,

Thanks for the posting the workbook. Are these cells being manually selected on "Sheet1" ?

Yes they are.

[Leith Ross]

Hello Dan,

The attached workbook has been modified to remove the unwanted characters when the user selects one ofr more cells on "Sheet1" in column "A".

I don't think this approach is a good one since you cannot edit column "A". That is, you can not add or delete text now, only modify what is there, unless you disable the macro.

Module1 Macro Code

Please Login or Register  to view this content.

Sheet1 - Selection Change Event Code

Please Login or Register  to view this content.

[Djbower1]

Hi Leigh,

Sorry for the very late reply.

You have saved my a huge amount of time and I am extremely grateful for all the help. The above worked well!

I feel extremely embarrassed to ask you another question but I have one more issue im sure will take you minutes to find a solution.

I have some months/years listed and I need them in UK format

CURRENT:

Please Login or Register  to view this content.

NEED TO BE

Please Login or Register  to view this content.

I dont know if this is even possible in excel as they are not formatted in a date format?

I have attached sheet for reference.

Year Example.xlsx

Many Thanks,

Dan

[Leith Ross]

Hello dan,

These "dates" are currently in yy/mm format and you want to change them to mm/yy format?

[Djbower1]

Hi Leith,

Yes that's correct.

Thanks,

Dan

[Leith Ross]

Hello Dan,

I ran this macro on the workbook you posted and it appears to run correctly. The real test will be on the actual data.

Please Login or Register  to view this content.

[Djbower1]

Hi Leigh,

This is nearly perfect

It seems to have issue if there is a single digit month in a cell. eg. 97/2 - 02/03 (I2 on sheet) It seems to skip these. Is there anyway your solution could be adapted to work with single digit month of should I manually change the culprit ones.

The data I have been working with is from Japan and the formatting is all over the place. EDIT: 1688 CELLS IN SINGLE DIGIT DATE FORMAT

Thank you again.

Dan

[Leith Ross]

Hello Dan,

You only need to change a single line the macro. It is in blue below. Copy and paste this line in your workbook.

Revised Macro Code

Please Login or Register  to view this content.

[Djbower1]

Just had a thought...(Which is dangerous)

Is it possible it find cells with only one digit after a "/" and enter a "0" after the "/" to make the values correct. Once this is done Your macro will work?

Thanks,

Dan

[Leith Ross]

Hello Dan,

Here is the updated macro to add the zero to either single digit months or years. This is included in the attached workbook.

Please Login or Register  to view this content.