Tag: Function

Change last character to subscript

Routine #18 from our series of text subroutines to use in EXCEL to change the last character of a string to subscript in the selected cells.

Code snippet below:

Sub last_charcter_subscript() 'last char subscript
Dim selected_range As Range
Set selected_range = Range(Selection.Address)

Dim old_value As String
For Each cell In selected_range
 old_value = vbNullString: old_value = cell.Value
 With cell
 .Font.Superscript = False
 .Font.Subscript = False
 .Characters(Start:=Len(.Value), Length:=1).Font.Subscript = True
 End With
Next
End Sub

You can see how to add this to Excel in order to use the code in this thread UPPER CASE


Change first character to subscript

Routine #17 from our series of text subroutines to use in EXCEL to change the first character of a string to subscript in the selected cells.

Code snippet below:

Sub first_charcter_subscript() 'first char subscript
Dim selected_range As Range
Set selected_range = Range(Selection.Address)

Dim old_value As String
For Each cell In selected_range
 old_value = vbNullString: old_value = cell.Value
 With cell
 
 .Font.Superscript = False
 .Font.Subscript = False
 .Characters(Start:=1, Length:=1).Font.Subscript = True
 End With
Next
End Sub

You can see how to add this to Excel in order to use the code in this thread UPPER CASE


Remove superscript/subscript

Routine #15 from our series of text subroutines to use in EXCEL to replace all superscript and subscript characters to normal characters in the selected cells.

Code snippet below:

Sub remove_super_subscript() 'remove_super_subscript
Dim selected_range As Range
Set selected_range = Range(Selection.Address)

Dim old_value As String
For Each cell In selected_range
 old_value = vbNullString: old_value = cell.Value
 
 cell.Font.Superscript = False
 cell.Font.Subscript = False
 
Next
End Sub

You can see how to add this to Excel in order to use the code in this thread UPPER CASE


Last character superscript

Routine #14 from our series of text subroutines to use in EXCEL to change the character character in the selected cells to superscript.

Code snippet below:

Sub last_charcter_superscript() 'last char superscript
Dim selected_range As Range
Set selected_range = Range(Selection.Address)

Dim old_value As String
For Each cell In selected_range
 old_value = vbNullString: old_value = CStr(cell.Value)
 With cell
 
 .Font.Superscript = False
 .Font.Subscript = False
 .Characters(Start:=Len(.Value), Length:=1).Font.Superscript = True
 End With
Next
End Sub

You can see how to add this to Excel in order to use the code in this thread UPPER CASE


First character superscript

Routine #13 from our series of text subroutines to use in EXCEL to change the first character in the selected cells to superscript.

Code snippet below:

Sub first_character_superscript() 'first char superscript
Dim selected_range As Range
Set selected_range = Range(Selection.Address)

Dim old_value As String
For Each cell In selected_range
 old_value = vbNullString: old_value = cell.Value
 With cell
 .Characters(Start:=1, Length:=1).Font.Superscript = True
 .Characters(Start:=2, Length:=(Len(.Value) - 1)).Font.Superscript = False
 End With
Next
End Sub

You can see how to add this to Excel in order to use the code in this thread UPPER CASE


Extract Alphanumeric characters

Routine #12 from our series of text subroutines to use in EXCEL to extract all the alphanumeric characters. Everything that does not fall under alphanumeric character will be automatically removed from the selected cells content.

Code snippet below:

Sub AlphaNumericOnly() 'Keep only alphanumeric characters
Dim selected_range As Range
Set selected_range = Range(Selection.Address)
Dim i As Integer
Dim strResult As String

Dim old_value As String
For Each cell In selected_range
 old_value = vbNullString: old_value = cell.Value
 
 For i = 1 To Len(old_value)
 Select Case Asc(Mid(strSource, i, 1))
 Case 48 To 57, 65 To 90, 97 To 122: 'include 32 if you want to include space
 strResult = strResult & Mid(strSource, i, 1)
 End Select
 Next
 cell.Value = strResult
 
 
Next

You can see how to add this to Excel in order to use the code in this thread UPPER CASE


Remove carriage return

Routine #11 from our series of text subroutines to use in EXCEL to remove all carrieage returns from a string (alt+enter). When applied to a range it will remove all carriage returns, ie. line brake character.

Code snippet below:

Sub remove_carriage_return() 'alt+enter character replace with one space
Dim selected_range As Range
Set selected_range = Range(Selection.Address)

Dim old_value As String
For Each cell In selected_range
 old_value = vbNullString: old_value = cell.Value
 cell.Value = Replace(old_value, vbLf, " ")
Next
End Sub

You can see how to add this to Excel in order to use the code in this thread UPPER CASE


Remove hyperlinks from cells

Routine #10 from our series of text subroutines to use in EXCEL to remove all hyperlinks from a string. When applied to a range it will remove all hyperlinks from the selected cells content.

Code snippet below:

Sub remove_hyperlinks() 'remove all hyperlinks
Dim selected_range As Range
Set selected_range = Range(Selection.Address)

Dim old_value As String
For Each cell In selected_range
 cell.Hyperlinks.Delete
Next
End Sub

You can see how to add this to Excel in order to use the code in this thread UPPER CASE


Remove numbers from string

Routine #9 from our series of text subroutines to use in EXCEL to remove all numbers from a string. When applied to a range it will remove all numbers from selected cells.

Code snippet below:

Sub remove_numbers_from_string() 'remove all numbers from string
Dim selected_range As Range
Set selected_range = Range(Selection.Address)
Dim i As Integer
Dim first_word As String

Dim old_value As String
For Each cell In selected_range
 For i = 0 To 9
 old_value = vbNullString: old_value = cell.Value
 cell.Value = Replace(old_value, i, "")
 Next
Next
End Sub

You can see how to add this to Excel in order to use the code in this thread UPPER CASE

 


Extract numbers from text

Routine #8 from our series of text subroutines to use in EXCEL to extract all numbers from a string. All the text contents in the selected range where this routine is applied will only have numbers, if numbers are not in the string the cell will be empty.

Code snippet below:

Sub Extract_Number_from_string() 'get numbers from text
Dim Length_of_String As Integer
Dim Current_Pos As Integer
Dim Temp As String
Dim old_value As String

Dim selected_range As Range
Set selected_range = Range(Selection.Address)

For Each cell In selected_range

old_value = vbNullString: old_value = cell.Value
Length_of_String = Len(old_value)
Temp = ""
For Current_Pos = 1 To Length_of_String
If (Mid(old_value, Current_Pos, 1) = "-") Then
 Temp = Temp & Mid(old_value, Current_Pos, 1)
End If
If (Mid(old_value, Current_Pos, 1) = ".") Then
 Temp = Temp & Mid(old_value, Current_Pos, 1)
End If
If (IsNumeric(Mid(old_value, Current_Pos, 1))) = True Then
 Temp = Temp & Mid(old_value, Current_Pos, 1)
 End If
Next Current_Pos

If Len(Temp) = 0 Then
 cell.Value = ""
Else
 cell.Value = Temp
End If

Next cell

End Sub

You can see how to add this to Excel in order to use the code in this thread UPPER CASE