I need to return characters between two dashes in a cell

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!

Find more posts tagged with

Comments

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)

=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

Sign in to join the conversation!

Quick Links

Trending discussions

Reference links break in formula
I want a formula that can reference updates on another sheet and add the info onto my sheet. The other sheet is a schedule that has job locations and PTO time for each individual on each day of the week. I want to know the dates that each individual on the sheet has an upcoming PTO, and for my sheet to update with 'PTO' on…
Can I remove "Select All" from Dropdown (Multi Select) form fields?
I have two Dropdown selectors in a form. Both need to allow users to select multiple options, but NEVER allow ALL options to be selected. At the top of the lists is a "Select All" options checkbox. I don't see any way to remove it in the form setup. I would expect it to be an optional feature that I could toggle on if I…
Smartsheet access
A collogue of mine had been using Smartsheet and is no longer with the Org. Im the administrator and can't find where the timelines and Gaant charts are. Is there someone at Smartsheet who could help me?