Adding Extract "fluff" Characters to a Value

Mstg007

Active Member
Joined
Dec 30, 2013
Messages
383
.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!
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=A1&REPT(<font color="Blue">"-",7-LEN(<font color="Red">A1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
you can also do

Code:
Function FluffyArentYa(s As String) As String
Const lenMax = 7
FluffyArentYa = s & String(lenMax - Len(s), "-")
End Function
 
Upvote 0
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)
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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