I need to return characters between two dashes in a cell

Options
edited 12/09/19

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!

• ✭✭✭✭✭✭
Options

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)

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

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
edited 10/19/18
Options

=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.

• ✭✭✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

Great solution Luke!

Have a fantastic weekend!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
Options

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

• Options

Thanks a lot. It worked great.

• edited 05/28/19
Options

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

• edited 05/28/19
Options

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 )

• Options

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)