# Find a Hyphen and return the rest

#### 34sweetness

##### Board Regular
I have a column with data:

04-29
04-1295
05-239
04-1

I just want the following in another column:

29
1295
239
1

how can I do this without manual entry?

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Try this:

=REPLACE(A1,1,FIND("-",A1),"")

Copy down.

Okay, thanks...

but...

What if the data has two hyphens?
Such as:

05-12-3568
05-1235-3698

and I want just:

3568
3698

Hi,
try

=MID(A1,FIND("/",SUBSTITUTE(A1,"-","/",2))+1,9*9)

hope this helps

jindon

Hi,
Both HOTPEPPER's and Jindon's formulas work great for each of their purposes.
However if your data can have either one or two hyphens in the same range, you can combine them to look for two hyphens & use jindon's formula, and if there's only one then use HOTPEPPER's.

=IF(ISERROR(MID(A1,FIND("/",SUBSTITUTE(A1,"-","/",2))+1,9*9)),(REPLACE(A1,1,FIND("-",A1),"")),MID(A1,FIND("/",SUBSTITUTE(A1,"-","/",2))+1,9*9))

Hope it helps,
Dan

Hi,
if A1 contains one or more "-" then try

=MID(A1,FIND("/",SUBSTITUTE(A1,"-","/",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,9*9)

it will pick the number after last "-"

rgds,
jindon

Try this:

=REPLACE(A1,1,FIND("~",SUBSTITUTE(A1,"-","~",LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))),"")

This is exactly the same type of problem I was trying to resolve when I asked for a function that would reverse the contents of a cell.

If you can reverse the contents, look for the 'first' hypen, determine it's position with the =FIND command and then subtract that value from the =LEN of the contents in it's proper direction. This gives you the last hypen's position in the original data and then you can use the =MID function to display everythig after it.

In fact you wouldn't need LEN and MID. If you had a "REVERSETXT" that worked you could just use the following:

=reversetxt(LEFT(reversetxt(A1),FIND("-",A1)-1))

sorry that previous formula won't work

it would need to be

=reversetxt(LEFT(reversetxt(A1),FIND("-",reversetxt(A1))-1))

Replies
4
Views
281
Replies
1
Views
114
Replies
3
Views
128
Replies
2
Views
287
Replies
4
Views
225

1,203,606
Messages
6,056,281
Members
444,854
Latest member
goethe168

### 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