2 sperate Font sizes in the same cell

mingandmong

Active Member
Joined
Oct 15, 2014
Messages
336
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
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,305
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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,247
Office Version
365
Platform
Windows
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
 

mingandmong

Active Member
Joined
Oct 15, 2014
Messages
336
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
 

mingandmong

Active Member
Joined
Oct 15, 2014
Messages
336
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
 

mingandmong

Active Member
Joined
Oct 15, 2014
Messages
336
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]
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,305
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
 

mingandmong

Active Member
Joined
Oct 15, 2014
Messages
336
[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]
 

mingandmong

Active Member
Joined
Oct 15, 2014
Messages
336
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
 

NdNoviceHlp

Well-known Member
Joined
Nov 9, 2002
Messages
2,305
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:

Forum statistics

Threads
1,077,850
Messages
5,336,742
Members
399,100
Latest member
darcob

Some videos you may like

This Week's Hot Topics

Top