Replacing text with symbols throughout a range of cells

[jackie68]

Is 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.

ray:

 

[Damon Ostrander]

Hi Jackie68,

Since Excel accepts Unicode characters, and the playing card suits are within the unicode character set, you can do this with a simple Find and Replace operation. You can get the characters to replace from the Windows Character Map utility on your computer. It is usually located under Start > Programs > Accessories > System Tools. You will find that the spade character has a hex 2663 character code in this set if you are in the English locale mode. You can use the Character Map to select and copy the characters and paste them into the Excel Find Replace textbox. Then you can simply select the range of cells you want to do the replacement on, then use the Edit > Find to do your replacement.

Damon

♣ ♦ ♠ ♥

 

[Ekim]

Put the following macro in the relevant sheet module. Simply input a letter (C = Clubs, D = Diamonds, H = Hearts, S = Spades) and hit the Enter key (see Notes at end).

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rng As Range
Set rng = Range("A1:A10")    ‘ CHANGE TO SUIT

With rng
    .Font.Name = "Symbol"
    .Font.Size = 12                     ‘ CHANGE TO SUIT
    .HorizontalAlignment = xlCenter
End With

If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, rng) Is Nothing Then
Select Case Target.Value

Case "C":
Target.Value = Chr(167)        ' Clubs

Case "D":
   Target.Value = Chr(168)     ' Diamonds
   Target.Font.ColorIndex = 3  ' Red

Case "H":
   Target.Value = Chr(169)     ' Hearts
   Target.Font.ColorIndex = 3  ' Red

Case "S":
   Target.Value = Chr(170)     ' Spades

Case Else
Exit Sub
End Select
End If

End Sub

Notes:
1. The macro applies to the range A1:A10 – adjust to suit.
2. The macro turns on the Symbol font, which is required to turn a certain character code into the symbols that represent Clubs, Diamonds, Hearts and Spades. AFAIK, the Symbol font is included with all Excel versions.
3. The font size is set to 12 point. Adjust to suit.
4. If you want to use upper or lower case to evoke the symbol, then change each line like:

Case "C":
Case “D”:
Case “H”:
Case “S”:

To:
Case "C", “c”:
Case “D”, “d":
Case “H”, “h”:
Case “S”, “s”:


HTH

Mike

 

[jackie68]

I forgot to mention; also in the cells are other numbers and letters.

For instance: 10S would be changed to 10 (spade symbol).

 

[Ekim]

Jackie,

The macro previously presented is an Event macro. If you type in say “C” (no quotes) and then hit the Enter key, the “C” will automatically turn into the symbol for a Club. However, if you already have data in a range, the macro, as presented, will not be triggered.

Assuming that you have already entered the data, then the macro needs to change to the following. Put this macro into a standard module (not a sheet module).

Option Explicit
Sub mySymbols()
Dim rng As Range
Dim cell As Variant
Dim ws As Worksheet

Set ws = Worksheets("Sheet1")       ‘ CHANGE TO SUIT
Set rng = ws.Range("A1:A20")       ‘ CHANGE TO SUIT 

For Each cell In rng
With cell
Select Case .Value

Case "C", "c":
   .Value = Chr(167)          ' Clubs
   .HorizontalAlignment = xlCenter
     With .Font
      .Name = "Symbol"
      .Size = 12
     End With
 
Case "D", "d":
   .Value = Chr(168)          ' Diamonds
   .HorizontalAlignment = xlCenter
     With .Font
      .Name = "Symbol"
      .Size = 12
      .ColorIndex = 3  ' Red
     End With
  
Case "H", "h":
   .Value = Chr(169)           '  Hearts
   .HorizontalAlignment = xlCenter
      With .Font
       .Name = "Symbol"
       .Size = 12
       .ColorIndex = 3  ' Red
     End With

Case "S", "s":
   .Value = Chr(170)         ' Clubs
   .HorizontalAlignment = xlCenter
     With .Font
      .Name = "Symbol"
      .Size = 12
     End With
   .HorizontalAlignment = xlCenter
End Select
End With
Next
End Sub

See this part of the macro:

Case "C", "c":
With Target
.Value = Chr(167) ' Clubs
With .Font
.Name = "Symbol"
.Size = 12
End With
.HorizontalAlignment = xlCenter
End With

In particular, see the first line:

Case "C", "c":
At the moment, if you have a “C” (uppercase) or a “c” (lowercase) in any cell in the range A1:A20, you will get the symbol for a Club. If you want other text/number(s) to also be a Club simply add that text/number(s), enclosed in quotes and separated by a comma, to the Case statement e.g. say you want “Jackie”, “Fred” and the number 10 to be shown as a Club, then change the first line of the above snippet to:

Case "C", "c", “Jackie”, “Fred”, “10”:
(the rest of the code for chr(167))

Any characters (text or numbers) that are not specified in the Case statement will stay as it is e.g. if you enter the letter A, B, Z (whatever) in the range A1:A20, it will remain as A, B or Z.


HTH


Mike

 

[jackie68]

My dilemma is further complicated now. I must be able to view this sheet on Documents To Go for my Palm.

It seems that Palm interprets the Symbol font differently (ie, a "spade" becomes the "trademark symbol").

Any ideas?

ray:

 

[Ekim]

I have no knowledge of a Palm but it looks like the Palm does not have the Symbols font (it seems reasonable that number of fonts will be limited for a Palm).

1. In cell A1, put “=CHAR(ROW())” - no quotes. Drag down to cell A256 (the first 30 or so cells will show a box – don’t worry about that).
2. Select column A and change the font to say Wingdings – scan the cells for the character that looks like a diamond, heart, spade or club. If no joy, change the font again etc. It could be that the Palm does not have a suitable font.

Note that on a worksheet the function is “=CHAR”, in VBA it is “Chr”. AFAIK, the Symbols font in a PC, will show:

CHAR(167) - Clubs
CHAR(168) – Diamonds
CHAR(169) - Hearts
CHAR(170) - Clubs

If you cannot find a suitable font, I suggest that you contact/search Microsoft regarding the possibility of downloading some font (assuming that one exists for a Palm).

Is there a Palm discussion group that you can subscribe to?

HTH

Mike