s there a way to replace specific letters in a range of cells with symbols?

NIKOG

New Member
Joined
Mar 5, 2021
Messages
13
s there a way to replace specific letters in a range of cells with symbols?

For instance:

Any "S" found would be replaced by the "spade" symbol.
Any "H" found would be replaced by the "heart" symbol.
Any "C" found would be replaced by the "club" symbol.
Any "D" found would be replaced by the "diamond" symbol.
please help me out step by step im really newby - Thanks infront guys
 
It's hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file (de-sensitized if necessary) that you can post here.
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
It's hard to work with a picture. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file (de-sensitized if necessary) that you can post here.
thisi s the file with the imported macro
 
Upvote 0
do need also to change anything on the second macro u send like the first 1 i changed the ranges ?
 
Upvote 0
Try:
VBA Code:
Sub ReplaceLetters()
    Application.ScreenUpdating = False
    Dim rng As Range, cnt As Long
    For Each rng In Range("A2", Range("A" & Rows.Count).End(xlUp))
        If rng Like "*h*" Or rng Like "*c*" Or rng Like "*d*" Or rng Like "*s*" Then
            rng.Replace "h", ChrW(9829)
            rng.Replace "d", ChrW(9830)
            rng.Replace "c", ChrW(9827)
            rng.Replace "s", ChrW(9824)
        End If
    Next rng
    For Each rng In Range("A2", Range("A" & Rows.Count).End(xlUp))
        For cnt = Len(rng) To 1 Step -1
            Select Case AscW(Mid(rng, cnt, 1))
                Case Is = 9829
                    Range("A" & rng.Row).Characters(cnt, 1).Font.ColorIndex = 3
                Case Is = 9830
                    Range("A" & rng.Row).Characters(cnt, 1).Font.ColorIndex = 44
                Case Is = 9827
                    Range("A" & rng.Row).Characters(cnt, 1).Font.ColorIndex = 4
                Case Is = 9824
                    Range("A" & rng.Row).Characters(cnt, 1).Font.ColorIndex = 1
            End Select
        Next cnt
    Next rng
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Try:
VBA Code:
Sub ReplaceLetters()
    Application.ScreenUpdating = False
    Dim rng As Range, cnt As Long
    For Each rng In Range("A2", Range("A" & Rows.Count).End(xlUp))
        If rng Like "*h*" Or rng Like "*c*" Or rng Like "*d*" Or rng Like "*s*" Then
            rng.Replace "h", ChrW(9829)
            rng.Replace "d", ChrW(9830)
            rng.Replace "c", ChrW(9827)
            rng.Replace "s", ChrW(9824)
        End If
    Next rng
    For Each rng In Range("A2", Range("A" & Rows.Count).End(xlUp))
        For cnt = Len(rng) To 1 Step -1
            Select Case AscW(Mid(rng, cnt, 1))
                Case Is = 9829
                    Range("A" & rng.Row).Characters(cnt, 1).Font.ColorIndex = 3
                Case Is = 9830
                    Range("A" & rng.Row).Characters(cnt, 1).Font.ColorIndex = 44
                Case Is = 9827
                    Range("A" & rng.Row).Characters(cnt, 1).Font.ColorIndex = 4
                Case Is = 9824
                    Range("A" & rng.Row).Characters(cnt, 1).Font.ColorIndex = 1
            End Select
        Next cnt
    Next rng
    Application.ScreenUpdating = True
End Sub
THANKS ALOT MAN U ARE THE BEST CHEERS : 1 last question
can "diamond" symbol be be blue color what i need to change in the macro

91489f762f297da20a13d229c7cdd5c0.png

Gyazo
 
Upvote 0
Try:
VBA Code:
Sub ReplaceLetters()
    Application.ScreenUpdating = False
    Dim rng As Range, cnt As Long
    For Each rng In Range("A2", Range("A" & Rows.Count).End(xlUp))
        If rng Like "*h*" Or rng Like "*c*" Or rng Like "*d*" Or rng Like "*s*" Then
            rng.Replace "h", ChrW(9829)
            rng.Replace "d", ChrW(9830)
            rng.Replace "c", ChrW(9827)
            rng.Replace "s", ChrW(9824)
        End If
    Next rng
    For Each rng In Range("A2", Range("A" & Rows.Count).End(xlUp))
        For cnt = Len(rng) To 1 Step -1
            Select Case AscW(Mid(rng, cnt, 1))
                Case Is = 9829
                    Range("A" & rng.Row).Characters(cnt, 1).Font.ColorIndex = 3
                Case Is = 9830
                    Range("A" & rng.Row).Characters(cnt, 1).Font.ColorIndex = 44
                Case Is = 9827
                    Range("A" & rng.Row).Characters(cnt, 1).Font.ColorIndex = 4
                Case Is = 9824
                    Range("A" & rng.Row).Characters(cnt, 1).Font.ColorIndex = 1
            End Select
        Next cnt
    Next rng
    Application.ScreenUpdating = True
End Sub
i fixed the colors myself ,Thanks alot man u are the best
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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