help on how to strip characters in a row using excel 2010

Excel 2010 64 bit

Have a spreadsheet with over 32,000 rows.

Column E has data that has several - (dashes) example 01-Item-name of file-test-one.abc

I need to strip out the first two dashes leaving this  name of file-test-one.abc
Column B will be the result column

A   B                    C          D                E
1                          1          1       01-item-name of file-test-one.abc
2                          1          2       02-item-name of file-test-two.abc
3                          2          1       01-example-name of example.abc
4                          2          1       02-example-name of example two.abc

AS you can see from the sample data some rows have only two dashes and others can have many more. I only need everything stripped up to the and including the second dash

A   B                                                        C          D                E
1   name of file-test-one                       1          1       01-item-name of file-test-one.abc
2   name of file-test-two                       1          2       02-item-name of file-test-two.abc
3   name of example                             2          1       01-example-name of example.abc
4   name of example two                     2          1       02-example-name of example two.abc

I can get rid of the .abc in a second pass of the data.

Column b is what I would like to have the result
LVL 23
Thomas GrassiSystems AdministratorAsked:
Who is Participating?
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

Same logic even shorter

=MID(E1,FIND("-",E1,FIND("-",E1)+1)+1,LEN(E1)-FIND("-",E1,FIND("-",E1)+1)-4)

Open in new window


Regards
0
 
Lee IngallsDirector of IT/TS, Quality and FinanceCommented:
Will column E always begin with a 2 digit number (01- or 02- etc)?
0
 
Thomas GrassiSystems AdministratorAuthor Commented:
Lee

thanks for responding

no they increment up 01 02 03 04 05 06 ...............
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Rgonzo1971Commented:
Hi,

pls try

=MID(E1,SEARCH("-",E1,SEARCH("-",E1)+1)+1,LEN(E1)-SEARCH("-",E1,SEARCH("-",E1)+1)-4)

Open in new window

Regards
0
 
BlueYonderCommented:
Try this formula
=RIGHT(RIGHT(E1, LEN(E1) - SEARCH("-",E1)), LEN(RIGHT(E1, LEN(E1) - SEARCH("-",E1))) - SEARCH("-",RIGHT(E1, LEN(E1) - SEARCH("-",E1))))
0
 
Dan CraciunIT ConsultantCommented:
I always had trouble understanding the "hacks" Excel experts go to for string manipulations. Hard (for me) to understand, hard to maintain.

Luckily, Patrick Matthews wrote an article in 2009 that showed how you can use the power of regular expressions from inside Excel: http://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1336-Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html

In this case, using the function RegExpReplace a possible solution looks like this:
=RegExpReplace(E2, "\d+-\w+-(.*).abc", "$1")

Open in new window

which means: search for a string with
- some digits (at least 1) followed by
- a dash, followed by
- some alphanumeric characters (at least 1), followed by
- a dash, followed by
- anything and ending with
- .abc

If found, take only that "anything".

Excel file attached.

HTH,
Dan
Q-28396673.xlsm
0
 
Thomas GrassiSystems AdministratorAuthor Commented:
Thanks for responding worked great
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.