Solved

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

Posted on 2014-03-25
7
169 Views
Last Modified: 2014-03-25
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
0
Comment
Question by:Thomas Grassi
7 Comments
 
LVL 8

Expert Comment

by:Lee Ingalls
ID: 39953039
Will column E always begin with a 2 digit number (01- or 02- etc)?
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 39953048
Lee

thanks for responding

no they increment up 01 02 03 04 05 06 ...............
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 39953051
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 9

Expert Comment

by:BlueYonder
ID: 39953057
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
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 39953069
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
 
LVL 34

Expert Comment

by:Dan Craciun
ID: 39953149
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
 
LVL 23

Author Closing Comment

by:Thomas Grassi
ID: 39953190
Thanks for responding worked great
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question