Transfer Multiple-Font Cells ??

monirg

monirg

Well-known Member
Joined
Jan 11, 2005
Messages
629
Hello;

1) Numerous cells in columns A and B on a w/s have a combination of two fonts each.
For example, cell A1 shows # 15 on the Formula Bar. The # sign is in "Wingdings 3" Font and 15 is in "Arial".
Cell A1 correctly displays |15 (i.e.; up arrow,space,15).

2) Other cells on the w/s reference those cells.
For example, cell C1:: =IF($J$5="Vortex",A1,B1)

When the above condition is true: Is there a way to return | 15 (i.e.; up arrow,space,15) in C1 , exactly as displayed in A1 ??

Thank you.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
I found a place on line that said this was not possible with out a messy macro That would require constant maintance.

Arial has an up arrow Character Code 2191

Try Insert > Symbol
Enter 2191 in the Charater Code Field.
 
Upvote 0
DaveMeade;

I believe the up arrow Character Code 219 as well as Character Code 227 & 211 are still Wingding 3 font. They're just different shapes than Wingding 3 Character Code 35 up arrow.
I tried it and it didn't work.
The answer may lie in using a macro as you suggested, which in this case wouldn't be worth the effort and complications!
There's a good chance, however, that some experts might suggest a workaround without the need for a macro.

Regards.
 
Upvote 0
Change the font in the Symbol Dialog box at the top to Arial
or which ever font the rest of the cell is using.
Then select character 2191, the Up arrow is Slightly different.
This is working for me.

As long as you are using common character such as arrows this should work. If your are using odd symbol (ie a phone) you will need a macro. If so is the wingding3 char always the first char followed by a space.
 
Upvote 0
DaveMeade;

a) You're correct! If a single font is used in the referenced cells in col A, then transfer poses no problem.
In other words, if the example cell A1 uses only Arial font (Arial: upwards arrow code 2191,space,15), then:
cell C1:: = IF($J$5="Vortex",A5,B5) would work fine.
However, not all Wingdings 3 characters have equivalents in Arial, and thus one still needs to know how to transfer mixed-font cell contents.

b) Let us consider another cell in col A.
Cell A5 shows & 10 on the Formula Bar. The & sign is in Wingdings 3 Font (code 38) and 10 is in "Arial".
Cell A5 correctly displays | 10 (i.e.; northeast arrow,space,10).
The wingdings 3 char always the first char followed by a space followed by a number.

c) So the question remains: How do you modify:
cell C5:: =IF($J$5="Vortex",A5,B5)
such that when the condition is true, cell C5 correctly displays | 10 (i.e.; northeast arrow,space,10) exactly as displayed in A5 ??
(Use of a "messy" macro is not an option!)

Regards.
 
Upvote 0
I was hoping this would work, It's Not :(
But by posting this maybe someone else will know how to make something simular work.

=IF($J$5="Vortex",WingDingFormat(A5),B5)

Code: 
Function WingdingFormat(rng As Range)
    WingdingFormat = rng.Value
    ActiveCell.Characters(Start:=1, Length:=1).Font.Name = "Wingdings 3"
End Function

I'll keep trying.
 
Upvote 0
DaveMeade;

Good thinking! It might work!
Please keep in mind that only the 1st char in A5 for example is in Wingdings 3 Font, and the remaining characters are in Arial.

I've the feeling it can't be done! Will see.

Regards.
 
Upvote 0
<TABLE style="WIDTH: 660pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=880 border=0 x:str><COLGROUP><COL style="WIDTH: 660pt; mso-width-source: userset; mso-width-alt: 32182" width=880><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17>New thought, Going back to my DOS days to get special character we had to modify the Font.

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17>I found Font creator at this link.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl25 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17>http://www.high-logic.com/fontcreator.html</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17>This is a fairly easy program to use (it has a 30 day free trial).</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17>I figured out want I needed in less than an hour. (good documentation)</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17>The Modified Font would need to be installed on all computers using your SS.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17>You can Pull up the Arial (or Any font) to start with give it a new "Family Name"</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17>i.e. Modified Arial, Arial Mod, Vortex, Cat whatever you want to name it.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17>Make sure it is a unique name.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17>Then Open Wingding3 right Click and Copy the Up-Arrow you wanted (# char code 35).</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17>Go back to the modified Font name and Right Click and Paste the font on a character you will not use.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17>In This case your using the Up-Arrow paste it on Char 2191 the Up-Arrow.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17>For the NE-Arrow go back to Wingding3 right Click and Copy the NE-Arrow you wanted (& char code 38).</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17>Go back to your modified font and Paste it on any Character that you will not use maybe 2194.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17>or even A5. I recommend trying to keep them in the same area so they are easier to find.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17>After You have saved all of your needed character save and install the font.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17 x:str="Be Careful DO NOT overwrite your Arial Font, or all of your programs that use Arial will show the included Special character i.e. ">Be Careful DO NOT overwrite your Arial Font, or all of your programs that use Arial will show the included Special character i.e. </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17>If you replace "&" with NE-Arrow every time you type "&" you would get the special character.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17>Select all cells on you SS and choose the Modified font.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17>and Insert the symbols as you did with the wingdings use the modified font to select from.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17>This should do the trick I know it is not an Excel Answer but it should resolve this issue.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; WIDTH: 660pt; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" width=880 height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>By putting all the wanted characters you need in a single Font, when you call the cell the character will stay the same.</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: #e0dfe3; BORDER-TOP: #e0dfe3; BORDER-LEFT: #e0dfe3; BORDER-BOTTOM: #e0dfe3; HEIGHT: 12.75pt; BACKGROUND-COLOR: white" height=17>Let me know if you feel this is an acceptable solution.

</TD></TR></TBODY></TABLE>
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Formula to count partial colored font using wildcards
2
Replies
12
Views
343
jMz
J
D
  • Question
Can this be solved with a formula?
Replies
6
Views
290
Armstrong_N14
A
N
  • Question
Duplicate cell to new cell using BOLDTEXT or Arial Black, font color, cell color, etc... as variables.
Replies
2
Views
337
Nvchicky
N
N
  • Solved
How to maintain font when referencing another cell?
Replies
4
Views
208
ned lud
N
Z
  • Solved
Formula to validate the right manager within manager levels in multiple rows
Replies
5
Views
89
Fluff
Fluff

Forum statistics

Threads
1,215,102
Messages
6,123,097
Members
449,096
Latest member
provoking

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top