2 sperate Font sizes in the same cell

mingandmong

Active Member
Joined
Oct 15, 2014
Messages
339
Hi Im using excel 2010

can anyone help me in in creating 2 sepertate size fonts in the same cell the first font is size 128 Arial & the second font size is 20 Arial

the formulas are in coulmn "E" & "F" (Vlookups from another sheet), i concatanate them with a space inbetween and word wrap so that the letter N is above and November is below in the

same cell which is in column"J"

i can manualy accomplish this by in the task bar highlighting the N and then Novemeber and manualy change the font size

however i have a lot of data in "E" & "F" and the data changes on a daily basis

any workaround is apprecied by either changing concatanate to another formula or a macro or vba

Many thanks
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Not real clear where your data is or what U actually want to change. Trial this. HTH. Dave
Code:
Function ChangeFont(myRange As Range, srchWord As String, FontName As String, FontSize As Integer)
'myrange is range to be changed, srchword is letter/word to be changed
'fontname is type of font, fontsize is size of font
Dim startChar As Integer, _
    lenColor As Integer, _
    nxtWord As Integer
Dim w As Range
Dim firstAddress As String
      With myRange
        Set w = .Find(srchWord, lookat:=xlPart, MatchCase:=True)
         If Not w Is Nothing Then
           firstAddress = w.Address
          Do
           startChar = InStr(1, w, srchWord)
              lenColor = Len(srchWord)
             With w.Characters(Start:=startChar, Length:=lenColor)
              .Font.Name = FontName
              .Font.Size = FontSize
             End With
              Set w = .FindNext(w)
          Loop While Not w Is Nothing And w.Address <> firstAddress
         End If
      End With
End Function
To operate...
Code:
Dim Myrng As Range
Set Myrng = Sheets("Sheet1").Range("j1:j10")
Call ChangeFont(Myrng, "N", "ariel", 128)
Call ChangeFont(Myrng, "ovember", "ariel", 20)
This will Change "N" to arial 128 and "ovember" to ariel 20 in sheet1 J1:J10. Adjust to suit. The letters/words entered to be changed are case sensitive.
 
Upvote 0
You cannot do that via a formula, but this macro should do what you want
Code:
Sub FontSize()
   
   Dim Cl As Range
   
   For Each Cl In Range("J2", Range("J" & Rows.Count).End(xlUp))
      With Cl
         .Value = .Value
         .Font.Name = "Arial"
         .Font.size = 20
      .Characters(1, 1).Font.size = 128
      End With
   Next Cl
   
End Sub
 
Upvote 0
Hi Dave i could not get this to run i copied both code into 2 modules and the macro did not run
i have posted again to try and make my request more clear
 
Upvote 0
Hi Fluff thankyou for your hard work.. the result is what i wanted, but it deletes my formula's in column j so i cannot use the same rows again
i will post another example there may be annother way to get my desired results thanks once again
 
Upvote 0
Hopefully this will be clearer im using excel 2010

I paste data into column "A" from another sheet
Row 1 is the current working data
Row 2 is modified i have now additional data in "F2" its a vlookup formula returning the value "November" and displaying it in column "J2" howver as you can see the font is incorrect
so i needed the letter N in Arail 128 and November in Arail 20 all in the same cell
Row 3 is the expected result but im using an extra Row 4 to display the word November
By ausing an extra row or merging the cells i cannot then paste the origanal data into column A due to cells merged
Another solution if a could copy & paste my data in Column A as a paste miss a row paste miss a row paste miss a row ect


Column "A" data below inc formulas
03A030
01B740
05A010

B2=TRIM(A2)
C2=LEFT(B2,2)
D2=RIGHT(B2,4)
E2=IFERROR(VLOOKUP(B2,Last!$A:$D,4,0),"")
H2=C2
I2=D2
J2=E2
B3=TRIM(A3)
C3=LEFT(B3,2)
D3=RIGHT(B3,4)
E3=IFERROR(VLOOKUP(B3,Last!$A:$D,4,0),"")
F3=IFERROR(VLOOKUP(B3,Last!$A:$E,5,0),"")
H3=C3
I3=D3
J3=CONCATENATE(E3,G3,F3)
B4=TRIM(A4)
C4=LEFT(B4,2)
D4=RIGHT(B4,4)
E4=IFERROR(VLOOKUP(B4,Last!$A:$D,4,0),"")
F4=IFERROR(VLOOKUP(B4,Last!$A:$E,5,0),"")
H4=C4
I4=D4
B5=TRIM(A5)
C5=LEFT(B5,2)
D5=RIGHT(B5,4)
E5=IFERROR(VLOOKUP(B5,Last!$A:$D,4,0),"")
H5=C5

<tbody>
</tbody>
HTML:
areaT

  
ABCDEFGHIJ103A03003A03003A030K 
 
03A030K201B74001B74001B740NNovember 
01B740N November305A01005A01005A010CCharlie 
05A010C4 
 
 
 
 
 
 
 
Charlie
Spreadsheet FormulasCellFormulaB1=TRIM(A1)C1=LEFT(B1,2)D1=RIGHT(B1,4)E1=IFERROR(VLOOKUP(B1,Last!$A:$D,4,0),"")H1=C1I1=D1J1=E1B2=TRIM(A2)C2=LEFT(B2,2)D2=RIGHT(B2,4)E2=IFERROR(VLOOKUP(B2,Last!$A:$D,4,0),"")F2=IFERROR(VLOOKUP(B2,Last!$A:$E,5,0),"")H2=C2I2=D2J2=CONCATENATE(E2,G2,F2)B3=TRIM(A3)C3=LEFT(B3,2)D3=RIGHT(B3,4)E3=IFERROR(VLOOKUP(B3,Last!$A:$D,4,0),"")F3=IFERROR(VLOOKUP(B3,Last!$A:$E,5,0),"")H3=C3I3=D3B4=TRIM(A4)C4=LEFT(B4,2)D4=RIGHT(B4,4)E4=IFERROR(VLOOKUP(B4,Last!$A:$D,4,0),"")H4=C4 
[url=http://www.excel-jeanie-html.de/index.php?f=1]Excel tables to the web - Excel Jeanie Html 4[/url]
 
Upvote 0
Hi mingandmong not sure why my code did't work for U unless the merge thing is doing something? Did U adjust the sheet Name and the call function is case specific. So...
Code:
Dim Myrng As Range
Set Myrng = Sheets("YourSheetName").Range("j1:j10")
Call ChangeFont(Myrng, "N", "ariel", 128)
Call ChangeFont(Myrng, "ovember", "ariel", 20)
Also, adjust the range to suit.
To get Fluff's code to not delete formulas. Just remove this line...
Code:
.Value = .Value
However, Fluff's code changes all values in J2:J& whatever not just the November.
Dave
 
Upvote 0
[CODEareaT


ABCDEFGHIJ
103A03003A03003A030K

03A030K
201B74001B74001B740NNovember
01B740N November
305A01005A01005A010CCharlie
05A010C
4







Charlie

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:186px;"><col style="width:72px;"><col style="width:34px;"><col style="width:50px;"><col style="width:26px;"><col style="width:57px;"><col style="width:16px;"><col style="width:64px;"><col style="width:412px;"><col style="width:152px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B1=TRIM(A1)
C1=LEFT(B1,2)
D1=RIGHT(B1,4)
E1=IFERROR(VLOOKUP(B1,Last!$A:$D,4,0),"")
H1=C1
I1=D1
J1=E1
B2=TRIM(A2)
C2=LEFT(B2,2)
D2=RIGHT(B2,4)
E2=IFERROR(VLOOKUP(B2,Last!$A:$D,4,0),"")
F2=IFERROR(VLOOKUP(B2,Last!$A:$E,5,0),"")
H2=C2
I2=D2
J2=CONCATENATE(E2,G2,F2)
B3=TRIM(A3)
C3=LEFT(B3,2)
D3=RIGHT(B3,4)
E3=IFERROR(VLOOKUP(B3,Last!$A:$D,4,0),"")
F3=IFERROR(VLOOKUP(B3,Last!$A:$E,5,0),"")
H3=C3
I3=D3
B4=TRIM(A4)
C4=LEFT(B4,2)
D4=RIGHT(B4,4)
E4=IFERROR(VLOOKUP(B4,Last!$A:$D,4,0),"")
H4=C4

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web - Excel Jeanie Html 4][/CODE]
 
Upvote 0
Hi Dave i did not change the sheet name... i will do so now and give it a go
i will also look at fluffs code and also try that
 
Upvote 0
I should have maybe included how to operate the Call function...
Code:
Sub Test
Dim Myrng As Range
Set Myrng = Sheets("YourSheetName").Range("j1:j10")
Call ChangeFont(Myrng, "N", "ariel", 128)
Call ChangeFont(Myrng, "ovember", "ariel", 20)
End Sub
Then run the sub. Dave
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,581
Messages
6,114,438
Members
448,573
Latest member
BEDE

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