How to remove characters in a cell that precede a specific hyphen
 

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?

How to remove characters in a cell that precede a specific hyphen
 

Presuming your data in A1 down looks like this:
ABC-12345-12344
ABC-12345-12355
etc

In B1:
=MID(A1,SEARCH("-",A1,SEARCH("-",A1)+1)+1,99)+0
Copy down to return the numbers after the 2nd hyphen as real numbers,
viz:
12344
12355
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"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?

How to remove characters in a cell that precede a specific hyphen
 

hi,
you said the number of characters vary. could you supply more examples of
the data?

Regards
FSt1

"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?

How to remove characters in a cell that precede a specific hyp
 

Max,

This works for most of my data but I have some data that has alpha
characters in them where this formula fails. Can you or FSt1 offer an
adaptation to this formula that allows for alpha characters?

Here's a few more examples

ABA-12345-12345
ABA-C12345-C12345
ABC-18L123-18L123
ABC-Q456789-Q456789



"Max" wrote:

Presuming your data in A1 down looks like this:
ABC-12345-12344
ABC-12345-12355
etc

In B1:
=MID(A1,SEARCH("-",A1,SEARCH("-",A1)+1)+1,99)+0
Copy down to return the numbers after the 2nd hyphen as real numbers,
viz:
12344
12355
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"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?

How to remove characters in a cell that precede a specific hyp
 

Here's a few mo

Here's a few more examples

ABA-12345M-12345M
ABA-C12345-C12345
ABC-18L123-18L123
ABC-Q456789JJ-Q456789JJ


"FSt1" wrote:

hi,
you said the number of characters vary. could you supply more examples of
the data?

Regards
FSt1

"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?

How to remove characters in a cell that precede a specific hyp
 

And one more example that has a zero.

ABC-07901-07901

I need the zero to stay with the number, thus the result to be: 07901.

thanks for your help

"FSt1" wrote:

hi,
you said the number of characters vary. could you supply more examples of
the data?

Regards
FSt1

"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?

How to remove characters in a cell that precede a specific hyphen
 

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

How to remove characters in a cell that precede a specific hyphen
 

If you always want to keep the characters after the last (no matter how many)
hyphen...

And you don't have to use a formula, you could use:

Select the range to fix
edit|replace
what: *- (asterisk, hyphen)
with: (leave blank)
replace all



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?

--

Dave Peterson

How to remove characters in a cell that precede a specific hyp
 

Ron, You are awesome. Great work. Its working exactly as I needed.

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

How to remove characters in a cell that precede a specific hyp
 

Try this:

=MID(A1,FIND("-",A1,FIND("-",A1)+1)+1,255)


--
Biff
Microsoft Excel MVP


"brantty" wrote in message
...
And one more example that has a zero.

ABC-07901-07901

I need the zero to stay with the number, thus the result to be: 07901.

thanks for your help

"FSt1" wrote:

hi,
you said the number of characters vary. could you supply more examples of
the data?

Regards
FSt1

"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?

How to remove characters in a cell that precede a specific hyp
 

Might help u

If A1 = ABA-12345M-12345M

IN B1 =MID(A1,SEARCH("-",A1,SEARCH("-",A1,SEARCH("-",A1)+1))+1,99)



On Apr 4, 7:10*am, brantty wrote:
Here's a few mo

Here's a few more examples

ABA-12345M-12345M
ABA-C12345-C12345
ABC-18L123-18L123
ABC-Q456789JJ-Q456789JJ



"FSt1" wrote:
hi,
you said the number of characters vary. could you supply more examples of
the data?

Regards
FSt1

"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?- Hide quoted text -

- Show quoted text -

How to remove characters in a cell that precede a specific hyp
 

You just need to remove the "+0" from the earlier,
viz use in B1, copied down:
=MID(A1,SEARCH("-",A1,SEARCH("-",A1)+1)+1,99)
(All extracts in col B will be in text)

Anyway, I see you have since received an answer you like
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"brantty" wrote in message
...
Max,

This works for most of my data but I have some data that has alpha
characters in them where this formula fails. Can you or FSt1 offer an
adaptation to this formula that allows for alpha characters?

Here's a few more examples

ABA-12345-12345
ABA-C12345-C12345
ABC-18L123-18L123
ABC-Q456789-Q456789

How to remove characters in a cell that precede a specific hyp
 

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

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

How to remove characters in a cell that precede a specific hyp
 

Use this

=MID(A1,FIND("-",A1)+1,99)



On Apr 4, 8:06*am, brantty wrote:
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- Hide quoted text -

- Show quoted text -

How to remove characters in a cell that precede a specific hyp
 

On Thu, 3 Apr 2008 19:34:01 -0700, brantty
wrote:

Ron, You are awesome. Great work. Its working exactly as I needed.

Ty

Glad to help. Thanks for the feedback.
--ron

How to remove characters in a cell that precede a specific hyp
 

On Thu, 3 Apr 2008 20:06:01 -0700, brantty
wrote:

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

Yep. It's just a matter of changing the "2" (for the 2nd hyphen) to a formula
that calculates the number of hyphens:



=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"-",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))))+1,255)
--ron

Hyphens problem
 

a1 = ABC-12345-12345

ANSWER IS

=mid(a1,54,5) and
answer = 12345

Hyphens problem
 

If that is the answer, then I am confused about what the question is? :-)
--
David Biddulph

<Suraj Noorsai wrote in message
...
a1 = ABC-12345-12345

ANSWER IS

=mid(a1,54,5) and
answer = 12345