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

[NIKOG]

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]

Change the range to suit your needs:

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]

Change the range to suit your needs:

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]

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]

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]

You are very welcome.

 

[NIKOG]

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]

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

Gyazo

 

[mumps]

Try:

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]

Try:

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

Gyazo

gyazo.com