Does anyone know where I might find an exhaustive UNICHAR table?

[Eloise T]

Does anyone know where I might find an exhaustive UNICHAR table (0 - 65533)?
Or how to make one on a spreadsheet?

 

[vletm]

Eloise T
If google is one of You don't ...
then You could check this

Unicode Chart

 

[Eloise T]

I Googled it and came up short.

I'm trying to add a Superscripted s and t after 1 to get 1st (with the st being Superscripted) and
to add a Superscripted n and d behind 2 to get 2nd (with the nd being Superscripted.)
The only Superscripted character I can find is the s ...=UNICHAR(738) yet as you will see in the attached Excel file, it is possible to have Superscript t, n, and d.

Copying and pasting =UNICODE("t") =UNICODE("n") and =UNICODE("d") using the Superscripts fails miserably, yielding 116, 110, and 100.

Please see attachment.

 

[vletm]

Eloise T
here:

Font.Superscript property (Excel)

Office VBA reference topic

docs.microsoft.com

or

Sub Do_It()
    With ActiveSheet
'   whole text
        .Range("B4").Font.Superscript = True
'   7th letter
        .Range("B4").Characters(7, 1).Font.Superscript = True
    End With
End Sub

 

[Eloise T]

How do I add it to this line where the WorksheetFunction.Unichar(x) singles out the letters s, t, n, and d? Is there a simpler way to do it?

.Replace "2*trips*EU*", "2 Trips - 1" & WorksheetFunction.Unichar(738) & WorksheetFunction.Unichar(116) & " trip-EU No Show/2" & WorksheetFunction.Unichar(110) & WorksheetFunction.Unichar(100) & " trip-Completed", xlWhole

Thanks for your patience and assistance.

 

[vletm]

Eloise T
Do You use above code once or millions times?
... You could solve those two 'Unichar' parts (U738116 & U110100) before actual use
.Replace "2*trips*EU*", "2 Trips - 1" & U738116 & " trip-EU No Show/2" & U110100 & " trip-Completed", xlWhole

 

[Eloise T]

I use it in a With loop that cycles about 10,000 times.

 

[vletm]

Your: Is there a simpler way to do it?
Yes ... now, You try to fix something which someone has done
Could that someone take care that while creating those 10,000 rows?
... then now need to do almost same task twice!
or
after one massive 'replace' ... do that only with rows which needs that task?

Did You test my sample code?

 

[Eloise T]

.Replace is only done in Columns which need to be done:
With Ws.[J3].Resize(Ws.Cells(Rows.Count, "D").End(xlUp).Row)

Yes, I tested the Sub Do_It() and it Superscripted the whole line rather than just st and nd
...so it did work. (See attachment) Of course, I just need to modify it so it only does st and nd

 

[vletm]

Eloise T
Did You skip those my comments with this code?
the 1st part works with the whole text ...
the 2nd part works with part of text ...
Of course, if You left the 1st part then
... could You modify that code as below?
... and after that You should able to modify .Characters(7,1) -part too
... ... where 7 means 7th letter and 1 means one letter to more suitable with Your needs
... ... ... .Characters(here_needed_number,2) - part
((( and You should use that code two times ... )))

Sub Do_It()
    With ActiveSheet
'   use this is You want to do this to whole text
''        .Range("B4").Font.Superscript = True
'   use this is You want to do this to 7th letter
        .Range("B4").Characters(7, 1).Font.Superscript = True
    End With
End Sub

 

[Eloise T]

Should I be trying to modify this line:
.Replace "2*trips*EU*", "2 Trips - 1" & U115116 & " trip-EU No Show/2" & U110100 & " trip-Completed", xlWhole

OR this line:

.Replace "2*trips*EU*", "2 Trips - 1" & WorksheetFunction.Unichar(115) & WorksheetFunction.Unichar(116) & " trip-EU No Show/2" & WorksheetFunction.Unichar(110) & WorksheetFunction.Unichar(100) & " trip-Completed", xlWhole

I pretty sure I don't need the Sub Do_It() / End Sub, but do I need the With / End with since .Replace is already in a With / End With loop?
If either line, how do I embed:
.Range("B4").Characters(7, 1).Font.Superscript = True

Can I embed just .Characters(7, 1).Font.Superscript = True .Characters(7, 3).Font.Superscript = False

 

[vletm]

Eloise T
before Your loop
>>> those numbers should be Your needed Unichar-code numbers - verify those -- there were some typos.
U738116 = WorksheetFunction.Unichar(738) & WorksheetFunction.Unichar(116) U110100 = WorksheetFunction.Unichar(110) & WorksheetFunction.Unichar(100)

in Your loop
.Replace "2*trips*EU*", "2 Trips - 1" & U738116 & " trip-EU No Show/2" & U110100 & " trip-Completed", xlWhole

case Superscript
Isn't there always two letters which Your code would work?
Because I cannot even guess Your code ... I have seen some of those ...
in Your loop
.cells(y,x).Characters(position_of_U738, 2).Font.Superscript = True .cells(y,x).Characters(position_of_U110, 2).Font.Superscript = True

(( where .cells(y,x) ... if needs ... y is as row and x is as number of column
(( position_of_U738 and position_of_U110 are numbers which tells letters positions in that cell
(( 2 means to effect with two letters ( st or nd ) which the code would 'superscript'

Your I pretty sure I don't need the Sub Do_It() / End Sub,
I'm pretty sure that each macro needs those.
That my sample was a macro ... which even could run.

 

[Eloise T]

U738116 = WorksheetFunction.Unichar(738) & WorksheetFunction.Unichar(116)
U110100 = WorksheetFunction.Unichar(110) & WorksheetFunction.Unichar(100)
Are you saying U738116 and U110100 can take the place of:
WorksheetFunction.Unichar(738) & WorksheetFunction.Unichar(116)
WorksheetFunction.Unichar(110) & WorksheetFunction.Unichar(100)
...or were you just creating an abbreviation as an example?


I'm sure you know this but for clarification:
The VBA code looks at Column J for: 2*trips*EU*
and replaces any occurrence with:

2 Trips - 1st trip-EU No Show/2nd trip-Completed

I want the st after 1 and the nd after 2 to be in Superscript.

My code is:
With Ws.[J3].Resize(Ws.Cells(Rows.Count, "D").End(xlUp).Row)
.Replace "2*trips*EU*", "2 Trips - 1" & WorksheetFunction.Unichar(115) & WorksheetFunction.Unichar(116) & " trip-EU No Show/2" &
WorksheetFunction.Unichar(110) & WorksheetFunction.Unichar(100) & " trip-Completed", xlWhole
End With

My code needs to be:
With Ws.[J3].Resize(Ws.Cells(Rows.Count, "D").End(xlUp).Row)
.Replace "2*trips*EU*", "2 Trips - 1" & [TURN ON Superscript] WorksheetFunction.Unichar(115) & WorksheetFunction.Unichar(116) [TURN OFF Superscript]& " trip-EU No Show/2" & [TURN ON Superscript] WorksheetFunction.Unichar(110) & WorksheetFunction.Unichar(100)
[TURN OFF Superscript] & " trip-Completed", xlWhole
End With
...but I don't know the proper syntax to do that.
I apologize if I was not clear before.

 

[Eloise T]

I tried this with no success: ...it replaces "2*trips*EU*" with simply FALSE ...not good.

.Replace "2*trips*EU*", "2 Trips – 1" & .Font.Superscript = True & "st" & .Font.Superscript = False & " trip-EU No Show/2" & .Font.Superscript = True & "nd" & .Font.Superscript = False & "trip-Completed", xlWhole

 

[vletm]

Eloise T
Based Your #13 and #14 replies, You should upload a sample Excel-file with full code which has worked.
Your the latest 'try' ... works as You've written Your code - but ... it's so far, which I have tried few times write.

 

[Eloise T]

The VBA macro is named "CleanUp_9"
The line of code is on "Ln 377, Col1"

Thank you.

 

[vletm]

Eloise T
Please, next time You should to offer needed details without a sample sheet.
I added few lines there and it seems to do needed.

 

[Eloise T]

I must apologize. In my effort to maintain "Sanitization" and 1 Mb size upload limitation, I left out some crucial points. The For/Next and IF/End If are changing more than it should. See "Sanitized" for clarity. Thank you again!

This is not the correct coding, but serves the point:
...i.e. find a comment and replace it with the corrected format.
(Sanitized has been updated (code still at Ln 377, Col 1))

>>> use code - tags <<<

                    .Replace "2*trips*EU*", "2 Trips - 1st trip-EU No Show/2nd trip-Completed", xlWhole

' IF SOMETHING WAS REPLACED PER INSTRUCTIONS IN THE LINE ABOVE, THEN:
                    For y = 1 To Ws.Cells(Ws.Cells.Rows.Count, "D").End(xlUp).Row
                        If .Cells(y, 1).Value <> Empty Then
                            .Cells(y, 1).Characters(12, 2).Font.Superscript = True
                            .Cells(y, 1).Characters(32, 2).Font.Superscript = True
                        End If
                    Next y

                  

                    .Replace "2*trips*Site*", "2 Trips - 1st trip-Site Not Ready/2nd trip-Completed", xlWhole

' IF SOMETHING WAS REPLACED PER INSTRUCTIONS  IN THE LINE ABOVE, THEN:                  
                    For y = 1 To Ws.Cells(Ws.Cells.Rows.Count, "D").End(xlUp).Row
                        If .Cells(y, 1).Value <> Empty Then
                            .Cells(y, 1).Characters(12, 2).Font.Superscript = True
                            .Cells(y, 1).Characters(36, 2).Font.Superscript = True
                        End If
                    Next y

 

[vletm]

Eloise T
Simplification means double or more work.
Should there be any connection with Your sentence 'If something was replaced...'?
If Yes then there should add something else too.
1Mb limit is good ... but ... that could solve this way.

 

[Eloise T]

Eloise T
Simplification means double or more work.
Should there be any connection with Your sentence 'If something was replaced...'?
If Yes then there should add something else too.
1Mb limit is good ... but ... that could solve this way.

I greatly appreciate you hanging in there with me for this. All works well. Thanks, vletm!