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
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,193
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

NIKOG

New Member
Joined
Mar 5, 2021
Messages
13
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
 

NIKOG

New Member
Joined
Mar 5, 2021
Messages
13
do need also to change anything on the second macro u send like the first 1 i changed the ranges ?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,193
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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
 

NIKOG

New Member
Joined
Mar 5, 2021
Messages
13
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
 

NIKOG

New Member
Joined
Mar 5, 2021
Messages
13
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
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,193
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
You are very welcome. :)
 

Forum statistics

Threads
1,176,500
Messages
5,903,415
Members
435,028
Latest member
excelvbanoob420

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
Top