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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,207
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Change the range to suit your needs:
VBA Code:
Sub ReplaceLetters()
    With Range("A:A")
        .Replace "H", ChrW(9829)
        .Replace "D", ChrW(9830)
        .Replace "C", ChrW(9827)
        .Replace "S", ChrW(9824)
    End With
End Sub
 

NIKOG

New Member
Joined
Mar 5, 2021
Messages
13
Change the range to suit your needs:
VBA Code:
Sub ReplaceLetters()
    With Range("A:A")
        .Replace "H", ChrW(9829)
        .Replace "D", ChrW(9830)
        .Replace "C", ChrW(9827)
        .Replace "S", ChrW(9824)
    End With
End Sub
sry man im totally new dont now where to change what :S - can u be a lit more specific - ANd where do do i import this vba code - thanks again
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,207
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Do the following: Save the workbook as a macro-enabled file. This will change its extension to "xlsm". Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. This macro will replace the four letters in column A with the corresponding symbols. You will need to change Range ("A:A") in the code to match the range you are interested in. After you change the range, press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key. If you need more help, please let me know.
 

NIKOG

New Member
Joined
Mar 5, 2021
Messages
13
Do the following: Save the workbook as a macro-enabled file. This will change its extension to "xlsm". Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. This macro will replace the four letters in column A with the corresponding symbols. You will need to change Range ("A:A") in the code to match the range you are interested in. After you change the range, press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key. If you need more help, please let me know.
thanks alot man ,cheers
 

mumps

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

NIKOG

New Member
Joined
Mar 5, 2021
Messages
13
Do the following: Save the workbook as a macro-enabled file. This will change its extension to "xlsm". Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top click 'Insert' and then click 'Module'. Copy and paste the macro into the empty code window that opens up. This macro will replace the four letters in column A with the corresponding symbols. You will need to change Range ("A:A") in the code to match the range you are interested in. After you change the range, press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key. If you need more help, please let me know.
it worked nicely but i need them to be in diffrent colours to mach the suit diamond =blue heart =read club=green spade- black - can u write a new code matching that that -thanks alot
 

NIKOG

New Member
Joined
Mar 5, 2021
Messages
13
they werked pretty nicel
it worked nicely but i need them to be in diffrent colours to mach the suit diamond =blue heart =read club=green spade- black - can u write a new code matching that that -thanks alot
6c9c2f8dcfe53b10e40aeaf043bd8282.png

Gyazo
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
12,207
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Try:
VBA Code:
Sub ReplaceLetters()
    Dim rng As Range
    For Each rng In Range("A1", Range("A" & Rows.Count).End(xlUp))
        Select Case rng.Value
            Case "H"
                rng.Replace "H", ChrW(9829)
                rng.Font.ColorIndex = 3
            Case "D"
                rng.Replace "D", ChrW(9830)
                rng.Font.ColorIndex = 32
            Case "C"
                rng.Replace "C", ChrW(9827)
                rng.Font.ColorIndex = 4
            Case "S"
                rng.Replace "S", ChrW(9824)
                rng.Font.ColorIndex = 1
        End Select
    Next rng
End Sub
 

NIKOG

New Member
Joined
Mar 5, 2021
Messages
13
Try:
VBA Code:
Sub ReplaceLetters()
    Dim rng As Range
    For Each rng In Range("A1", Range("A" & Rows.Count).End(xlUp))
        Select Case rng.Value
            Case "H"
                rng.Replace "H", ChrW(9829)
                rng.Font.ColorIndex = 3
            Case "D"
                rng.Replace "D", ChrW(9830)
                rng.Font.ColorIndex = 32
            Case "C"
                rng.Replace "C", ChrW(9827)
                rng.Font.ColorIndex = 4
            Case "S"
                rng.Replace "S", ChrW(9824)
                rng.Font.ColorIndex = 1
        End Select
    Next rng
End Sub
i floowed instruction nothing happened - check out the screenshot and tell me if i did somethign wrong
 

Forum statistics

Threads
1,176,597
Messages
5,903,950
Members
435,059
Latest member
grumpytrashpanda

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