CODEWITHSUNDEEP
excelexcel-formula
Pham X. Bach
Pham X. Bach

Reputation: 5442

Excel get substring from nth position up to the end of string

So How to get substring from nth position up to the end of string?

Input at cell A1 Name: Thomas B.

Expected output: Thomas B.

I know some way to do it but I wonder if there are other elegant ways than them? (some kind of =RIGHT(A1, -6)....)

  1. =MID(A1, 6, 999999) //999999 looks not so good

  2. =MID(A1, 6, LEN(A1) - 5) //must calculate 2 times, first get len, then get substring, seems too much works?

Upvotes: 6

Views: 19955

Answers (4)

VBasic2008
VBasic2008

Reputation: 54807

REPLACE

As Dominique already wrote: 'Why don't you just replace the first six characters by an empty string?'

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

I've done some time measuring, but the difference is less than a second at 50000 records (for LEFT, MID, REPLACE & SUSTITUTE). So I'm afraid ELEGANCE is all you're going to get.

A Small Study

I created this study due to the fact that when you say from the n-th character, your n-th character is 7 (your MID-s are wrong), but you want to remove the first n-1 (6) characters. So depending on how you formulate your question, you might have a different approach in RIGHT or MID, and you will remember REPLACE and SUBSTITUTE or you may not.

enter image description here

Small Study Formulas for A1 (*) and B1 (#, ?, *)

Get String From N-th Character to the End, e.g. 7

=RIGHT(A1,LEN(A1)-(B1-1))
=RIGHT(A1,LEN(A1)-B1+1)

=RIGHT(A1,LEN(A1)-6)

=MID(A1,B1,LEN(A1)-(B1-1))
=MID(A1,B1,LEN(A1)-B1+1)
=MID(A1,B1,LEN(A1))
=MID(A1,7,LEN(A1)-6)

=MID(A1,7,LEN(A1))

Remove N First Characters of a String, e.g. 6

=RIGHT(A1,LEN(A1)-B1)
=RIGHT(A1,LEN(A1)-6)
=MID(A1,B1+1,LEN(A1)-B1)
=MID(A1,B1+1,LEN(A1))
=MID(A1,7,LEN(A1)-6)
=MID(A1,7,LEN(A1))

Get String After a Character e.g. " "

=RIGHT(A1,LEN(A1)-(FIND(B1,A1)))
=RIGHT(A1,LEN(A1)-(FIND(" ",A1)))
=MID(A1,FIND(B1,A1)+1,LEN(A1)-FIND(B1,A1))
=MID(A1,FIND(B1,A1)+1,LEN(A1))
=MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1))
=MID(A1,FIND(" ",A1)+1,LEN(A1))

Get String After a String e.g. ": "

=RIGHT(A1,LEN(A1)-(FIND(B1,A1)+LEN(B1))+1)
=RIGHT(A1,LEN(A1)-FIND(B1,A1)-LEN(B1)+1)
=RIGHT(A1,LEN(A1)-FIND(": ",A1)-LEN(": ")+1)
=MID(A1,FIND(B1,A1)+LEN(B1),LEN(A1)-(FIND(B1,A1)+LEN(B1))+1)
=MID(A1,FIND(B1,A1)+LEN(B1),LEN(A1)-FIND(B1,A1)-LEN(B1)+1)
=MID(A1,FIND(B1,A1)+LEN(B1),LEN(A1))
=MID(A1,FIND(": ",A1)+LEN(": "),LEN(A1)-FIND(": ",A1)-LEN(": ")+1)
=MID(A1,FIND(": ",A1)+LEN(": "),LEN(A1))

Back to Remove N First Characters of a String, e.g. 6

=SUBSTITUTE(A1,LEFT(A1,6),"",1)

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

Upvotes: 9

z32a7ul
z32a7ul

Reputation: 3777

Another possibility is that you create a constant with the value 2^31-1 (=2147483647), which is the maximum signed integer value on 32-bit systems, and you give it a nice name, like MaxInt, then your first formula will be efficient and nice looking, too:

=MID(A1, 6, MaxInt)

You can add the Name with Ctrl+F3. If you are interested in fast calculations, giving it as 2147483647 rather than 2^31-1 may have some (very little) advantage.

Upvotes: 1

Dominique
Dominique

Reputation: 17493

Why don't you just replace the first six characters by an empty string?

=SUBSTITUTE(A1;LEFT(A1;6);"";1)

Upvotes: 2

Spainey
Spainey

Reputation: 402

Well, both of your methods already work, but you could also use this one:

=RIGHT(A1,LEN(A1)-6)

(you nearly had this one in your own question)

or this one:

=TRIM(MID(A1,FIND(":",A1)+1,100))

(the FIND() function returns the numeric position of a search string, so is great for doing dynamic substrings)

Upvotes: 4

Related Questions