CODEWITHSUNDEEP
excelvba
user2683996
user2683996

Reputation: 133

Return the characters after Nth character in a string

I need help! Can someone please let me know how to return the characters after the nth character?

For example, the strings I have is "001 baseball" and "002 golf", I want my code to return baseball and golf, not the number part. Since the word after the number is not always the same length, I cannot use = Right(String, n)

Any help will be greatly appreciated

Upvotes: 12

Views: 137583

Answers (6)

Gary Wheatcroft
Gary Wheatcroft

Reputation: 1

=REPLACE(A1,1,n,"") - where n is the number of chars you want to remove, by @barry houdini is the most elegant method.

You can use MID(string,start,nn) - where nn is larger than the longest string and it will just use the remaining chars, or

MID(string,start,LEN(string) - which grabs the total length of the original string if you want to make sure.

Upvotes: 0

Alex P
Alex P

Reputation: 12489

If your numbers are always 4 digits long:

=RIGHT(A1,LEN(A1)-5) //'0001 Baseball' returns Baseball

If the numbers are variable (i.e. could be more or less than 4 digits) then:

=RIGHT(A1,LEN(A1)-FIND(" ",A1,1)) //'123456 Baseball’ returns Baseball

Upvotes: 21

barry houdini
barry houdini

Reputation: 46341

Another formula option is to use REPLACE function to replace the first n characters with nothing, e.g. if n = 4

=REPLACE(A1,1,4,"")

Upvotes: 0

Aaron Thomas
Aaron Thomas

Reputation: 5281

Since there is the [vba] tag, split is also easy:

str1 = "001 baseball"
str2 = Split(str1)

Then use str2(1).

Upvotes: 1

tigeravatar
tigeravatar

Reputation: 26640

Alternately, you could do a Text to Columns with space as the delimiter.

Upvotes: 3

rory.ap
rory.ap

Reputation: 35270

Mid(strYourString, 4) (i.e. without the optional length argument) will return the substring starting from the 4th character and going to the end of the string.

Upvotes: 10

Related Questions