Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: 2 sperate Font sizes in the same cell

  1. #1
    Board Regular
    Join Date
    Oct 2014
    Posts
    336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 2 sperate Font sizes in the same cell

    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

  2. #2
    Board Regular NdNoviceHlp's Avatar
    Join Date
    Nov 2002
    Location
    Manitoba Canada
    Posts
    2,283
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: 2 sperate Font sizes in the same cell

    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.

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,120
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: 2 sperate Font sizes in the same cell

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular
    Join Date
    Oct 2014
    Posts
    336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2 sperate Font sizes in the same cell

    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

  5. #5
    Board Regular
    Join Date
    Oct 2014
    Posts
    336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2 sperate Font sizes in the same cell

    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

  6. #6
    Board Regular
    Join Date
    Oct 2014
    Posts
    336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2 sperate Font sizes in the same cell

    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
    HTML Code:
    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]

  7. #7
    Board Regular NdNoviceHlp's Avatar
    Join Date
    Nov 2002
    Location
    Manitoba Canada
    Posts
    2,283
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: 2 sperate Font sizes in the same cell

    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

  8. #8
    Board Regular
    Join Date
    Oct 2014
    Posts
    336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2 sperate Font sizes in the same cell

    [CODEareaT

    A B C D E F G H I J
    1 03A030 03A030 03 A030 K 03 A030 K
    2 01B740 01B740 01 B740 N November 01 B740 N November
    3 05A010 05A010 05 A010 C Charlie 05 A010 C
    4 Charlie

    Spreadsheet Formulas
    Cell Formula
    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

    Excel tables to the web - Excel Jeanie Html 4][/CODE]

  9. #9
    Board Regular
    Join Date
    Oct 2014
    Posts
    336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 2 sperate Font sizes in the same cell

    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

  10. #10
    Board Regular NdNoviceHlp's Avatar
    Join Date
    Nov 2002
    Location
    Manitoba Canada
    Posts
    2,283
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: 2 sperate Font sizes in the same cell

    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 by NdNoviceHlp; Feb 4th, 2018 at 08:55 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •