PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

CONTENTS

HOW TO REMOVE CHARACTERS FROM STRING IN VBA?

INTRODUCTION

STRINGS is the specific term given to the normal Text in the programming languages.
Strings are very important component in any program as this is the only part of the language through which the program communicate with the user.
We ask the user for the input through a STRING.
We process the user input.
We return the output to the user through a STRING.
So we can understand the importance of Strings in VBA.
Many times we need to handle strings very carefully for which we need to know several types of manipulations so that we can make our application can communicate effectively.
” STRINGS ARE THE ONLY WAY THROUGH WHICH OUR APPLICATIONS COMMUNICATE WITH THE USER”
 
In this article we’ll learn about the ways to remove specific characters from the given text or strings using VBA with the help of an example.
 

 

WHAT IS A STRING IN VBA

Simple text is a String.
String is a collection of characters. As we know programming languages considers the characters as the basic unit. So when characters combine STRINGS are formed.
e.g.
“WELCOME TO GYANKOSH.NET” is a String.

REMOVING ANY SPECIFIC CHARACTER/S FROM STRING IN VBA

OBJECTIVE

REMOVE CHARACTER c FROM THE GIVEN STRING-” CAT CAUGHT A MOUSE “

PLANNING THE SOLUTION

Whenever we need to inspect any string, we need to go character wise. As we need to eliminate c, we need to check every character sequentially.

  1.  Get the input from the cell to a string.
  2. Examine the string one by one.
  3. If the character is c , skip it, if others, leave them as such.
  4. Return back the string.
 
 

CODE

‘ gyankosh.net
‘ REMOVING CHARACTER C FROM THE GIVEN STRING

Sub replaceCharacter()

Dim temp1 As String
Dim temp2 As String
temp2 = “”

temp1 = Range(“F3”).Value ‘getting value from cell

Dim I As Integer

For I = 1 To Len(temp1)
If Not UCase(Mid(temp1, I, 1)) = “C” Then ‘scanning character if its equal to c or not
temp2 = temp2 + Mid(temp1, I, 1)

End If
Next I

Range(“H3”).Value = “The given string after removing c is ” + temp2 ‘Sending the text back to cell.

End Sub

EXPLANATION

We declare  temp1 and temp2 as string variable and get the input from the cell. temp2 is kept for putting in the characters, which we don’t need to skip.
Start the loop with variable i =1 upto the length of temp1.
Apply if UPPER CASE of MID(TEMP1,I,1) which gives the ith character, is equal to c or not. If yes, skip it, and if not, just attach it with the temporary string temp2.
Output is taken by putting the value back to cell H3.

RUNNING THE CODE AND OUTPUT

HOW TO REMOVE CHARACTERS FROM STRING IN VBA?
REMOVE CHARACTER IN A STRING USING VBA

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

[email protected]

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

  • PICTURES ARE TOO SMALL?? Don’t worry, Click them, and pinch zoom to see them properly. All pictures are in high resolution.
  • KINDLY LIKE OUR FACEBOOK PAGE BY CLICKING HERE  AND IF WE NEED TO IMPROVE, KINDLY SEND US THE FEEDBACK ON [email protected]
  • Didn’t get what you were looking for?? Just put your question in the chat box present in the Right Lower corner. Name and email id [No spamming, just for the answer of your requirement] are needed.
*CLICK ANYWHERE ELSE TO CLOSE THIS WINDOW
%d bloggers like this: