How to remove characters in a cell that precede a specific hyp
Try this:
=MID(A1,FIND("~~",SUBSTITUTE(A1,"-","~~",LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,255)
Assumes there is at least 1 hypen.
--
Biff
Microsoft Excel MVP
"brantty" wrote in message
...
Ron,
I found some cells that were different,. Instead of looking for the 2nd
hypen, can you adapt a formula to keep all data after the LAST hyphen?
Ty
"Ron Rosenfeld" wrote:
On Thu, 3 Apr 2008 17:16:24 -0700, brantty
wrote:
I have a column of cells that have excess data that needs removed. The
#of
characters vary. I'm needing a formula to delete all the characters
that
precede the 2nd hyphen as well as delete the 2nd hyphen.
Ex. [ABC-12345-12345]
I need the cell to read [12345]
Can anyone provide a formula that can remove this data?
Assuming that your brackets [ ] are not really part of the value, then:
=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"-",CHAR(1),2))+1,255)
will return everything after the 2nd hyphen.
--ron
|