Inserting the letter c at the end of 5 characters in a cell

[indi visual]

I have 5 characters I would like the letter "c" placed at the end of. I've created a mark as complete button. My goal is to click the mark as complete button that adds a "c" at the end of characters in the cell.


I don't believe I can use a formula for this, because I can only place a "c" when the job is marked as complete.


I'm afraid a formula will just place a "c" down the whole column on everything.


A click sub macro button code that can offset 5 characters spaces would be golden.

 

[Hui]

Have a look at John Walkenbachs, Power Utility Pack

It has a very neat Text Tool which will help you

http://spreadsheetpage.com/


There maybe other free utils on the net.

 

[Hui]

You can add a very simple macro to a Code Module in VBA

and then link this to a Button or Shape on your worksheet


Goto the cell/cells where you want to add a C and click the shape/button which has the macro assigned to it.

[pre]

Sub Addc()
Dim c As Range
For Each c In Selection
c.Value = c.Value + "c"
Next
End Sub

[/pre]

Note the above code will allow you to select several cells at once and apply a "c" to the end of all of them.

 

[cyrilz]

Another trick is to use 5 column, and concatening with CONCAT() all the previous columns the latest will containt "C" mhen the job is done


Example :

[pre]

A   B   C   D   E   F   G
Concatenation.
1   2   3   -   -   C   =CONCAT(A2:F2)   ===> give "123--C"

[/pre]

Regards

 

[indi visual]

Hui and Cyril.


Thanks a mill.


I ended up using this:[pre]Sub INSERT_C()<br />
Dim myRange As Range<br />
Set myRange = Range("G10&#34<br />
For Each Cell In myRange.Cells<br />
Cell.Value = Left(Cell.Value, 10) & String(10 - Len(Cell.Value), " &#34 & "c"<br />
Next<br />
End Sub[/pre]


I modified the Len value accordingly to my needs.


As it happened, Hui helped me solve something else that eliminated the need for me to even need this in the first place.


That's what this forum is all about, finding more crisp and efficient solutions. The shortest point is a straight line.