# Extracting only numbers in string, on right of dash

#### judwall

##### New Member
I am working with model numbers similar to this: ESLC148-12T

What formula can I use to extract just the numbers (in this case,"12") from the right of the dash?

I am using =REPLACE(W4,1,FIND("-",W4),"") to show me everything after the dash but I can't find how to go from there.

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### FDibbins

##### Well-known Member
This works...
=LOOKUP(99^99,--("0"&MID(MID(A1,FIND("-",A1)+1,99),MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},MID(A1,FIND("-",A1)+1,99)&"0123456789")),ROW(\$1:\$10000))))

I will see if I can simplify it a bit

#### tusharm

##### MrExcel MVP
Lightly tested with 1, 2, or 3 digits after the dash...

Use the array formula
Code:
``=MID(W4,FIND("-",W4)+1,MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},W4),0))-FIND("-",W4))``

To enter an array formula complete data entry with CTRL+SHIFT+ENTER rather than just the ENTER or TAB key. If done correctly, *Excel* will show the formula in curly brackets { and }
I am working with model numbers similar to this: ESLC148-12T

What formula can I use to extract just the numbers (in this case,"12") from the right of the dash?

I am using =REPLACE(W4,1,FIND("-",W4),"") to show me everything after the dash but I can't find how to go from there.

#### judwall

##### New Member
This worked!! Thank you so much!!

#### Rick Rothstein

##### MrExcel MVP
I am working with model numbers similar to this: ESLC148-12T

What formula can I use to extract just the numbers (in this case,"12") from the right of the dash?

I am using =REPLACE(W4,1,FIND("-",W4),"") to show me everything after the dash but I can't find how to go from there.
One example is usually not enough to tell us what your data really looks like. My questions are...

1) Is that dash always located in the 8th character position?

2) Is the dash always followed by exactly 2 digits?

3) Is the text after that number always exactly one character long?

Depending on your answer to those 3 questions, there might be a much simpler and more efficient formula available.

#### judwall

##### New Member
One example is usually not enough to tell us what your data really looks like. My questions are...

1) Is that dash always located in the 8th character position?

2) Is the dash always followed by exactly 2 digits?

3) Is the text after that number always exactly one character long?

Depending on your answer to those 3 questions, there might be a much simpler and more efficient formula available.

I realized that I should have given more details after I posted it... sorry about that. To answer your questions:

1) No, the dash in not always in the 8th position.

2) No, it ranges from 1-4digits.

3) The text is always one character long HOWEVER there isn't always text after the dash - often it is just 1-3 numbers.

Examples of other model numbers:
SLC38-12
EGHB218-24
HB1410-20T

#### Rick Rothstein

##### MrExcel MVP
2) No, it ranges from 1-4digits.

3) The text is always one character long HOWEVER there isn't always text after the dash - often it is just 1-3 numbers.
Based on your answer to #3, I assume you meant 1-4 characters in #2, not 1-4 digits. Given that, this formula should work...

=LOOKUP(9999,--LEFT(MID(W4,FIND("-",W4)+1,4),{1,2,3,4}))

Last edited:

#### István Hirsch

##### Well-known Member
Or try this:

=LOOKUP(10^4,0+MID(W4,FIND("-",W4)+1,{1,2,3,4}))

#### judwall

##### New Member
I got 3 answers that work - awesome! Thanks everyone

#### AlKey

##### Active Member
and this
=-LOOKUP(1,-LEFT(MID(A1,FIND("-",A1)+1,4),ROW(\$1:\$4)))

Replies
9
Views
151
Replies
14
Views
279
Replies
16
Views
223
Replies
23
Views
364
Replies
3
Views
64

1,191,690
Messages
5,988,105
Members
440,126
Latest member
duque00

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back