Find and Replace Part of cell

Incidently if you are replacing "/1" with "" then "8/11" would become "81" not "8/1".

Anyhoo... is the 'fraction' the only thing in the cell, or is this as part of more text?

Do you have an example of some cell contents?

Why do you need a macro to do that? You can do it from the worksheet - Ctrl F.

i am using ctrl H (to replace) but it is a part of a macro. Example now attached.

Ok the following code will do what you want, but there are some 'issues' that I have mentioned below.

Please Login or Register  to view this content.

The problems here are that your original fractions (in your example) actually have 'space' characters each side of them, which can stop the 'Like' compare operating correcting. This is why the 'Trim' function is being used to remove the leading and trailing spaces.

The next issue is that (as I think you have discovered), you can't enter something like "12/1" into an Excel cell without Excel thinking it knows better and mangling the data into something else, (in my experience a date of the format "12-Jan".

There are a few ways to cope with this, and it's all down to your personal preference, and what the rest of your code requires.

- You can format the cells as 'Fraction'. This will display the fractions correctly in the cell BUT if you try to access the cell.value then you will get the decimal result of the fraction, not the text itself.
- You can 'pad' the fraction text with spaces, which seems to be the method you have used to enter the data in your example, BUT then you have to use the 'Trim' function to obtain just the fraction.
- You can format the cell as Text BEFORE putting the value into it, BUT this will result in Excels little green flags showing that a number is being stored as text in the cell.

I have currently coded above to Format the cells as text before putting in the values.
If you wish to use spaces to pad instead you would need to modify the above code;

Remove the RED line, (and manually format the destination cells back to 'General').
Change the BLUE line as follows:

Please Login or Register  to view this content.

becomes

Please Login or Register  to view this content.

Change the GREEN line as follows:

Please Login or Register  to view this content.

becomes

Please Login or Register  to view this content.