Solved

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

Posted on 2014-03-25
7
171 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 50

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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 50

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 35

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

726 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