Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

2 Answers 2

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.