Find a Hyphen and return the rest

[34sweetness]

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?

 

[Scott Huish]

Try this:

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

Copy down.

 

[34sweetness]

Okay, thanks...

but...

What if the data has two hyphens?
Such as:

05-12-3568
05-1235-3698

and I want just:

3568
3698

 

[jindon]

Hi,
try

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

hope this helps

jindon

 

[HalfAce]

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

 

[jindon]

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

 

[Scott Huish]

Try this:

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

 

[Andrew70]

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.

 

[Dutchy]

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

 

[Dutchy]

sorry that previous formula won't work

it would need to be

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