asked on

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

Lee Ingalls

Will column E always begin with a 2 digit number (01- or 02- etc)?

Member_2_6492660_1

ASKER

Lee

thanks for responding

no they increment up 01 02 03 04 05 06 ...............

Rgonzo1971

Hi,

pls try

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

Open in new window

Regards

BlueYonder

Try this formula
=RIGHT(RIGHT(E1, LEN(E1) - SEARCH("-",E1)), LEN(RIGHT(E1, LEN(E1) - SEARCH("-",E1))) - SEARCH("-",RIGHT(E1, LEN(E1) - SEARCH("-",E1))))

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dan Craciun

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: https://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

Member_2_6492660_1

ASKER

Thanks for responding worked great