TEXT formatting withing single quote

Screenshot 2018-12-15 14.22.38.png
Screenshot_9.jpg

Macro :

 Sub formating()


Dim L As Long
 L = Range("A1", Range("A1").End(xlDown)).Rows.Count
    ActiveSheet.Select
 Columns("B:B").Select
   Selection.NumberFormat = "General"
    Range(Cells(2, 2), Cells(L, 2)).FormulaR1C1 = _
       "=IFERROR(REPLACE(UPPER(LEFT(RC[-1],1))&MID(LOWER(RC[-1]),2,999),FIND(""'"",UPPER(LEFT(RC[-1],1))&MID(LOWER(RC[-1]),2,999)),2,UPPER(MID(UPPER(LEFT(RC[-1],1))&MID(LOWER(RC[-1]),2,999),FIND(""'"",UPPER(LEFT(RC[-1],1))&MID(LOWER(RC[-1]),2,999)),2))),UPPER(LEFT(RC[-1],1))&MID(LOWER(RC[-1]),2,999))"
   

 End Sub

Hi Team,

I want to format text as in second image. I dont know much about vba. Can you please advise.


Thanks,
Gajjo

Welcome to the forum!

Two things:

1. You posted in the wrong section - I have moved the thread for you.
2. You did not use code tags (see our forum rules) - I have added them for you this time.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

I cannot help you with the VBA - somebody else will.

thats mistake. I want it as 'The Soldering Iron'

what if i need to update changes in same cell.

This code work exactly as i wanted but i need value to be updated in same cell.
and this code not making first word of sentence capital if it is in small case.

is this ok to use below code if i need value to be updated in same cell.

Option Explicit
Sub formatting()
Dim c As Range, n As Long, SplitStr As Variant

Range("B1").Select
Selection.EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
ActiveCell.Value = "Normal Text"

For Each c In Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    SplitStr = Split(c.Value, "'")
    c.Offset(, 1) = IIf(UBound(SplitStr) Mod 2 = 1, "''", "") & c
    For n = 0 To UBound(SplitStr)
        If n Mod 2 <> (UBound(SplitStr) Mod 2) Then
            On Error Resume Next
            c.Offset(, 1).Replace "'" & SplitStr(n) & "'", IIf(n = 0, "", "'") & WorksheetFunction.Proper(SplitStr(n)) & "'"
        End If
    Next
        c.Offset(, 1) = UCase(Left(c.Offset, 1)) & Right(c.Offset(, 1), Len(c.Offset(, 1) - 1))
Next

Range(Cells(1, 1), Cells(Rows.Count, 1)).Select
Selection.Delete Shift:=xlToLeft
End Sub

yes, that hidden ' issue. I have created macro which make it to visiable . I select them and run below macro

Sub Coln()
Dim rng As String
Dim myrng As Range
rng = Selection.Address
Set myrng = Range(rng)
If Range(cellrng).PrefixCharacter = "'" Then
     Range(cellrng).Value = "''" & Range(cellrng)
End If
End Sub

Im testing your macro.
Thank you very much.

Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 6)