Find and replace font of a particular character type in a sheet

[exl]

Hello,

I have a sheet with multiple instances of ` in various text strings.

I want to find and replace all instances of ` only with the font 'Rupee Forandian'

Actually recently the rupee symbol was launched and there is no keyboard symbol for that...

When I try the excel replace function (CRTL+H) with the format function, it changes the font of the entire text string, while I want it to change only the character `

 

[Rick Rothstein]

Hello,

I have a sheet with multiple instances of ` in various text strings.

I want to find and replace all instances of ` only with the font 'Rupee Forandian'

Actually recently the rupee symbol was launched and there is no keyboard symbol for that...

When I try the excel replace function (CRTL+H) with the format function, it changes the font of the entire text string, while I want it to change only the character `

Is the text containing that character the result of a formula? If so, then you change part of the text the formula is displaying... you can only do that with (text) constants. There is a work-around using VB event code to construct the text that your formula is doing now (meaning the formulas would be removed), but to write it up for you, we would need to see the formula you are currently using and what cells you have the formula in (there might be additional questions, but the answer to those two would allow us to start developing the code for you). Also, where did you get the font "Rupee Forandian" as I do not see that in my list of Excel fonts (without having that font available, any code we develop would be a guess because there is no real way to test it)?

 

[exl]

Is the text containing that character the result of a formula? If so, then you change part of the text the formula is displaying... you can only do that with (text) constants. There is a work-around using VB event code to construct the text that your formula is doing now (meaning the formulas would be removed), but to write it up for you, we would need to see the formula you are currently using and what cells you have the formula in (there might be additional questions, but the answer to those two would allow us to start developing the code for you). Also, where did you get the font "Rupee Forandian" as I do not see that in my list of Excel fonts (without having that font available, any code we develop would be a guess because there is no real way to test it)?

Thanks Rick for replying.
The text is not a part of the formula, its a simple text string which I have typed. Basically my the excel sheet is mostly filled up with text.
As I explained there is no 'Rupee' symbol on the keyboard like dollar or euro, we have to install this font 'Rupee Forandian' to get the symbol. The font can be downloaded from here

 

[Rick Rothstein]

The text is not a part of the formula, its a simple text string which I have typed.

Okay, good, then you can use this macro to change the "backward apostrophes" to your Rupee Foradian symbol. Select the cells you want to change first and then run this macro...

Sub InsertRupeeForandianSymbol()
  Dim X As Long, Cell As Range
  For Each Cell In Selection
    For X = 1 To Len(Cell.Value)
      If Mid(Cell.Value, X, 1) = "`" Then Cell.Characters(X, 1).Font.Name = "Rupee Foradian"
    Next
  Next
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (InsertRupeeForandianSymbol) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for.

 

[exl]

Dear Rick,

Worked like a dream, thank you so much....

 

[exl]

Rick,

Sorry to bother you, but I was wondering if this VBA could be used for the same purpose in Powerpoint 2007

 

[Rick Rothstein]

Rick,

Sorry to bother you, but I was wondering if this VBA could be used for the same purpose in Powerpoint 2007

I do not use Powerpoint, so I cannot answer you with a 100% guarantee, but my guess is no... Characters is a property of the Range object and I doubt that Powerpoint uses Ranges, if it even has them, in the same way that Excel does. That is not to say you cannot do what you want using the built-in facility of Powerpoint, I just do not think my macro would be usable either directly or even with minor modifications.

 

[exl]

I do not use Powerpoint, so I cannot answer you with a 100% guarantee, but my guess is no... Characters is a property of the Range object and I doubt that Powerpoint uses Ranges, if it even has them, in the same way that Excel does. That is not to say you cannot do what you want using the built-in facility of Powerpoint, I just do not think my macro would be usable either directly or even with minor modifications.

Thanks Rick, its always a pleasure interacting with you...

 

[perpectuals]

Is the text containing that character the result of a formula? If so, then you change part of the text the formula is displaying... you can only do that with (text) constants. There is a work-around using VB event code to construct the text that your formula is doing now (meaning the formulas would be removed), but to write it up for you, we would need to see the formula you are currently using and what cells you have the formula in (there might be additional questions, but the answer to those two would allow us to start developing the code for you). Also, where did you get the font "Rupee Forandian" as I do not see that in my list of Excel fonts (without having that font available, any code we develop would be a guess because there is no real way to test it)?

Hi Sir..

Need ur help. Same problem as mentioned by the owner of this thread except despite of Rupee symbol, a small rectangle is being displayed.

I went for Find & Replace option. Its not working out as it changes font of entire sheet.

Went thru VB Code method as u mentioned above. But when i copy that small rectangle box, "?" symbol is displaying in vb code window. So it also doesn't work.

Its just a text string and not a formula. Excel Version is 2010.

Try to change the settings in Regional - keyboard - indian keyboard and all. But still it doesn't work.

Request your help on this. Thanks much.

Regards.
Thilak N

 

[Rick Rothstein]

Went thru VB Code method as u mentioned above. But when i copy that small rectangle box, "?" symbol is displaying in vb code window. So it also doesn't work.

Select one of the cells with that character at the beginning and then execute this line of code in the Immediate Window and tell me what number prints out...

? AscW(Left(Selection.Text, 1))