I need to return characters between two dashes in a cell

AWood

Hello!

I need to return characters, whatever the length, from between two dashes in a cell. For example:

Column A

A79-199A-2018

A76-185AK-2017

A77-1000A1-2017

 

And then i would need a formula in Column B to return only 199A, 185AK, and 1000A1. If anyone could help me with this, I would greatly appreciate it. Thank you!

Andrée Starå

Hi,

Try this.

You will need a few helper columns for this to work.

Start, End & Characters are helper columns that you can hide when everything is working. 

Start

=FIND("-"; [Column A]@row)

End 

=FIND("-"; [Column A]@row; Start@row + 1)

Characters 

=End@row - Start@row - 1

Result

=MID([Column A]@row; Start@row + 1; Characters@row)

Please see the attached link/screenshot for more information.

Characters between dashes

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

I hope this helps you!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

L_123

=LEFT(RIGHT(A@row, LEN(A@row) - VALUE(FIND("-", A@row))), FIND("-", RIGHT(A@row, LEN(A@row) - VALUE(FIND("-", A@row)))) - 1)

 

Where the column name that contains the value you are analyzing is A

 

The easy way to make this work is change the columns name to A, then post this formula where you want it. Then change the column name back to what you want it to be.

Mike Wilday

Hi There, 

Luke's formula is cleaner as you won't need helper columns but I was only able to make it work when I used @row instead of @cell.

=LEFT(RIGHT(A@row, LEN(A@row) - VALUE(FIND("-", A@row))), FIND("-", RIGHT(A@row, LEN(A@row) - VALUE(FIND("-", A@row)))) - 1)

You can then easily drag this down through the column to get it from any row. 

L_123

yeah I had a brainfart. I edited it so it is correct thanks.

Andrée Starå

That's what's so great with the community. Always be learning!

Great solution Luke!

Have a fantastic weekend!

Mike Wilday

So true! It is fun to learn from other people's solutions! 

somashekar.tr79831

HI,

 

Please anyone will help with the formula.  I need to extract the text which is appearing before Hyphen:

BB_Boots & Barkley

DES_Designer (LTO)

In above examples I need to extract only BB and DES.

 

Thank you

L_123

=left([Column Reference]1,find("_",[Column Reference]1)-1))

somashekar.tr79831

Thanks a lot. It worked great.

 

James Finlay

Hi Everyone, 

Riffing off Luke's formula above, I'm trying to extract the numbers between "JWID" and "_OT" in the following text string:

JW_TV_16507_ASP28650_TEST_JWID154210_OT_en-US_178_1080_2997

Formula I'm trying but getting an #INVALID VALUE error (I may have the order wrong):

=LEFT(RIGHT(Name89, LEN(Name89) - VALUE(FIND("_OT", Name89))), FIND("JWID", RIGHT(Name89, LEN(Name89) - VALUE(FIND("_OT", Name89)))) - 1)

Best,

James

James Finlay

Never mind - with the assistance of a talented colleague he got it working:

=LEFT(RIGHT(Name89, LEN(Name89) - VALUE(FIND("JWID", Name89))-3), FIND("_OT", RIGHT(Name89, LEN(Name89) - VALUE(FIND("JWID", Name89)))) - 4 )

Romano el Polako

Hey,

Just adding some rework to support others in need:

START:

=FIND("/",[Date/Time Opened]@row)

 

END

=FIND("/", [Date/Time
Opened]@row, START@row+ 1)

 

Or:
=FIND("/",[Date/Time Opened]@row, FIND("/", [Date/Time Opened]@row) + 1)

 

CHARACTERS

=END@row- START@row- 1

 

Or:

 

=FIND("/", [Date/Time
Opened]@row,
FIND("/", [Date/Time Opened]@row) + 1) - FIND("/", [Date/Time
Opened]@row) - 1

 

DAY -
RESULT

 

=MID([Column
A]@row; Start@row + 1; Characters@row)

 

Or

 

=MID([Date/Time
Opened]@row, FIND("/", [Date/Time
Opened]@row) + 1,
FIND("/",[Date/Time Opened]@row, FIND("/", [Date/Time Opened]@row) + 1) - FIND("/", [Date/Time
Opened]@row) - 1)

https://community.smartsheet.com/discussion/34146/i-need-to-return-characters-between-two-dashes-in-a-cell