VBA: Remove Characters from Left or Right Side of Variable Length String
If you always know the length of a string, it’s easy to remove characters from it. Example: If you have a string that is 10 characters and you want to remove 1 character from the Left side, simply return the right 9 characters:
msgbox Right(Mystring, 9)
This doesn’t work for a variable length string, or one which you don’t know beforehand it’s length. In this case you can use the formula (Length – N) to designate how many characters to extract:
MsgBox Right(Mystring, Len(Mystring) – 1)
Where 1 is the number of characters to remove from the left side of the string. This will return the string minus the left most character.
To remove characters from the right side of a string, replace Right with Left
Hi Mark!
I tend to use the Mid function.
eg.
MsgBox Mid(Mystring, 2)
Cheers,
Rob
Hi Rob,
Never thought of mid like that, just extracting from the middle. The Mid doesn’t work when removing characters from the right side of a string, unless I missed it.
Hi Mark,
The MID function has an optional argument to specify the length
Msgbox Mid(MyString,1, Len(Mystring) – 1)
Thanks andy. I see it now.
Must be a Left(brain) Right(brain) thing, which function people naturally gravitate towards, or something like that.
I tend to use the Mid fuction, it’s the only real option when you’r not sure of the lenght of the stringd involved.
Speaking of left a right sides – which way does the sun shine on this blog? both the left and right edges have shadows – not that it matters any of course, but i just noticed it!
LOL, the suns reflecting off my forehead straight onto the screen. Actually I noticed the odd shadows when I put this layout up and thought “nobody will notice”.
The tan and blues are from taking a color picker to a screenshot of Excel.
I’m thinking of tweaking the layout again for the fun of it. -edit-
This site is my first leap onto the web, so everything from playing with advt’s, to layouts, to sydicating this site on others (exceltip (not anymore)), to some content: is an experiment just as much as for function.
Mark, it’s my opinion that this is the prettiest excel blog on the web!!! – i like the layout as it is – hey but if you wanta change it don’t let me stop you!
MIE is coming along nicely also, thanks for the big link. Let me know when you want a reciprocal.
I know your not much on doing a blog yourself, however an RSS feed when you add articles would be awesome. From an RSS reader’s standpoint anyway.
thanks – I’m (to coin an English phase) “blowing out of my as#) at the mo, but I have just got http://www.methodsinexcel.co.uk booked, and will be moving the
Excel Left Function VS. Excel Mid Function
Excel Left Function wins the GoogleFight
lol
Help if you can
I need to remove the hyphen from the middle of a text string
example
I have 4011-5555
I need 40115555
is there a way to remove the character from a range of cells withou manually removing it
I’m also looking for this function if anyone knows it. I’m having to make do with the following at the moment,
oldstring = “111-111″
newstring = Left(oldstring, InStr(oldstring, “-”) – 1) & Right(oldstring, InStr(oldstring, “-”) + 1)
Its a bit naff and only works if theres 1 instance of “-”.
thanks.
sorry, should read.
newstring = Left(oldstring, InStr(oldstring, “-”) – 1) & Right(oldstring, InStr(oldstring, “-”) – 1)
@Steve – If I understand what your after, this is easy with Replace:
newString = Replace(oldString, “-”, “”)
I am looing at the post for removing text from a string MsgBox Right(Mystring, Len(Mystring) – 1). This places a msgbox on screen but does not change the text in the cell. How do I change the text string in the cell?