Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Adding Extract "fluff" Characters to a Value

  1. #1
    Board Regular
    Join Date
    Dec 2013
    Posts
    364
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Adding Extract "fluff" Characters to a Value

    .Adding Extra Characters

    I have the following data in list:
    100
    100RD
    100RD_A
    100RD_B
    101
    102
    102RD
    103
    103RD
    103RD_A
    etc...
    I have a sort VBA setup which works fine with the numbers and just the "RD" part. I am wondering how I can have a formula look at a value and add a "fluff" characters to the value.
    example
    100----
    100RD--
    100RD_A
    100RD_B
    101----
    102----
    102RD--
    103----
    103RD--
    103RD_A
    I tried to sort with the above values and it worked.

    So I am wanting the fomula to look at the value and add "-" dependent on the missing extra values. I sure hope this makes some sense! Thanks for any help!

  2. #2
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,315
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Extract "fluff" Characters to a Value

    Worksheet Formulas
    CellFormula
    B1=A1&REPT("-",7-LEN(A1))


  3. #3
    Board Regular
    Join Date
    Jul 2014
    Location
    Ontario, Canada
    Posts
    286
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Extract "fluff" Characters to a Value

    and if you want a vba solution,
    Code:
    Sub addDash()
        For Each cell In Selection
            cell.Value = cell & Application.WorksheetFunction.Rept("-", 7 - Len(cell))
        Next
    End Sub
    'realized i didnt need an If

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    29,964
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Adding Extract "fluff" Characters to a Value

    Quote Originally Posted by Gilliam View Post
    and if you want a vba solution,
    Code:
    Sub addDash()
        For Each cell In Selection
            If Len(cell) - 7 <> 0 Then
                cell.Value = cell & Application.WorksheetFunction.Rept("-", 7 - Len(cell))
            End If
        Next
    End Sub
    You can also do a VBA solution without using a loop...
    Code:
    Sub AddFluff()
      Dim Addr As String, MaxLen As Long
      Const FluffChar As String = "-"
      Addr = "A1:A" & Cells(Rows.Count, "A").End(xlUp).Row
      MaxLen = Evaluate("MAX(LEN(" & Addr & "))")
      Range(Addr) = Evaluate("IF(LEN(" & Addr & ")=0,"""",LEFT(" & Addr & "&REPT(""" & FluffChar & """," & MaxLen & ")," & MaxLen & "))")
    End Sub
    Note: Your code will fail if the length of any cell is greater than 7 plus it outputs 7 dashes for an empty cell.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    Board Regular
    Join Date
    Dec 2013
    Posts
    364
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Extract "fluff" Characters to a Value

    Thank you guys so much for the variations!

  6. #6
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,315
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Extract "fluff" Characters to a Value

    you can also do

    Code:
    Function FluffyArentYa(s As String) As String
    Const lenMax = 7
    FluffyArentYa = s & String(lenMax - Len(s), "-")
    End Function

  7. #7
    Board Regular
    Join Date
    Dec 2013
    Posts
    364
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Extract "fluff" Characters to a Value

    If you dont mind, I might of well ask, What formula would you recommend for splitting the 100 numbers and letters up?
    I have the following:
    Column Y (this i can not make work.)
    Code:
    =MID(V7,1,FIND(Z7,V7)-1)
    Column Z (I believe this works.)
    Code:
    =RIGHT(V6,4)

  8. #8
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,315
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Extract "fluff" Characters to a Value

    do you always have the 100s followed by text?

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    29,964
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Adding Extract "fluff" Characters to a Value

    Quote Originally Posted by Mstg007 View Post
    If you dont mind, I might of well ask, What formula would you recommend for splitting the 100 numbers and letters up?
    I have the following:
    Column Y (this i can not make work.)
    Code:
    =MID(V7,1,FIND(Z7,V7)-1)
    Column Z (I believe this works.)
    Code:
    =RIGHT(V6,4)
    IF your numbers are always going to be 100, you can put 100 in your number cell and, assuming your text is in cell V7, put

    =MID(V7,6,99)

    in the text cell. However, if the 100 is not constant throughout your cells and if there are no other number emedded withn the text after the leading number, then you can use these...

    W7: =-LOOKUP(0,-LEFT(V7,ROW(INDEX(A:A,1):INDEX(A:A,LEN(V7)))))

    X7 = MID(V7,LEN(W7)+1,99)
    Last edited by Rick Rothstein; Aug 22nd, 2014 at 02:53 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #10
    Board Regular
    Join Date
    Dec 2013
    Posts
    364
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Adding Extract "fluff" Characters to a Value

    Occasionally we get it with 1, 2, 3, etc... in the same fashion as above.

User Tag List

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
  •  


DMCA.com