Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm pretty sure you can do this easier in VB, but Im looking to do this with formulas in EXCEL.

I would like to strip off the first x number of 'part's in a given string within EXCEL. A 'part' is a split of the full string.

EXAMPLE In C#:

"A.BB.CCC.DDDD.EEEEE".Split(new char[]{'.'});
// This yields 5 parts, "A", "BB", "CCC", "DDDD", "EEEEE"

So I need a formula that would take in '2', and would yield CCC.DDDD.EEEEE, by stripping off the first 2 'part's of the string.

A.BB.CCC.DDDD.EEEEE --> CCC.DDDD.EEEEE given 2
A.BB.CCC.DDDD.EEEEE --> DDDD.EEEEE given 3
A.BB.CCC.DDDD.EEEEE --> A.BB.CCC.DDDD.EEEEE given 0
share|improve this question

migrated from stackoverflow.com Jul 4 '13 at 8:49

This question came from our site for professional and enthusiast programmers.

    
I'm confused on what you are asking exactly. Can you lay out your question better? – crush Jul 3 '13 at 16:17
    
How are you given the number of given .? – Jerry Jul 3 '13 at 16:32
    
Updated the question to try to explain better, help? – Tizz Jul 3 '13 at 16:40
up vote 3 down vote accepted

Well, you could use:

=MID(A2, FIND("@", SUBSTITUTE(A2, ".", "@", 2))+1, LEN(A2))
                                            ^

The one I pointed above is what states the 'given'. Put 2 for 2 . and 3 for 3 ..

Or reference it to a cell where you'll get to know how many dots you need.

EDIT: As per edit in question, we could add an IF() to check for 0. Let's say that the number is stored in cell A1:

=IF(A1 = 0, A2, MID(A2, FIND("@", SUBSTITUTE(A2, ".", "@", A1))+1, LEN(A2)))
share|improve this answer
    
Although this solves my immediate need, there is no guarantee that a '@' wont be in my original string ... infact any character could be in the string (except for '.', which is the seperator) +1 anyways, but not the perfect solution! – Tizz Jul 3 '13 at 16:59
1  
@Tizz You can use CHAR(1) instead, which should be less common than @ :) – Jerry Jul 3 '13 at 17:02
    
Interesting ... I suppose CHAR(1) would never be typed ;) I think you've sold me now! :) – Tizz Jul 3 '13 at 17:05
1  
@Tizz Also, as per your update, I guess one could add an IF() to check for the value. Updated. – Jerry Jul 3 '13 at 17:05

After two dots:

=MID(B5,SEARCH(".",B5,SEARCH(".",B5)+1)+1,100)

share|improve this answer
1  
@Jerry's is better... – Tim Williams Jul 3 '13 at 16:37
    
This solves the inherent issue with @Jerry's solution, however, what if I want to strip off 100 parts? Thats a lot of cut and pasting... – Tizz Jul 3 '13 at 17:01
    
Yes - that's why Jerry's is better... Seems like a good candidate for a VBA user-defined function though. – Tim Williams Jul 3 '13 at 17:46

You must log in to answer this question.