how to selectively delete digits in a number?
 

I have a long list of numbers that I need to change.
the original number is:
1961-04-10-2691

I need it to be:
610410-2691

in other words, I need the first two numbers to go away and the first two
minus signs (-) to go away.

can anyone help me?

how to selectively delete digits in a number?
 

=SUBSTITUTE(SUBSTITUTE(MID(A2,3,99),"-","",1),"-","",1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"spreadsheet monkey" wrote in
message ...

I have a long list of numbers that I need to change.
the original number is:
1961-04-10-2691

I need it to be:
610410-2691

in other words, I need the first two numbers to go away and the first two
minus signs (-) to go away.

can anyone help me?

how to selectively delete digits in a number?
 

=MID(A2,3,2)&MID(A2,6,2)&RIGHT(A2,7)

"spreadsheet monkey" wrote:


I have a long list of numbers that I need to change.
the original number is:
1961-04-10-2691

I need it to be:
610410-2691

in other words, I need the first two numbers to go away and the first two
minus signs (-) to go away.

can anyone help me?

how to selectively delete digits in a number?
 

I tried this one, but it didnt work. this may be because I use excel 2003. I
have had problems with this before. sometimes it helps to substitute the (,)
with (;). although it didnt this time. I tried the other response, which
worked, so then I forgot about this one. in other words, it might work, I
just dont know how!

"Bob Phillips" wrote:

=SUBSTITUTE(SUBSTITUTE(MID(A2,3,99),"-","",1),"-","",1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"spreadsheet monkey" wrote in
message ...

I have a long list of numbers that I need to change.
the original number is:
1961-04-10-2691

I need it to be:
610410-2691

in other words, I need the first two numbers to go away and the first two
minus signs (-) to go away.

can anyone help me?

how to selectively delete digits in a number?
 

FYI: Bob's formula worked OK for me with Excel 2003.

"spreadsheet monkey" wrote:

I tried this one, but it didnt work. this may be because I use excel 2003. I
have had problems with this before. sometimes it helps to substitute the (,)
with (;). although it didnt this time. I tried the other response, which
worked, so then I forgot about this one. in other words, it might work, I
just dont know how!

"Bob Phillips" wrote:

=SUBSTITUTE(SUBSTITUTE(MID(A2,3,99),"-","",1),"-","",1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"spreadsheet monkey" wrote in
message ...

I have a long list of numbers that I need to change.
the original number is:
1961-04-10-2691

I need it to be:
610410-2691

in other words, I need the first two numbers to go away and the first two
minus signs (-) to go away.

can anyone help me?

how to selectively delete digits in a number?
 

I use 2003 too.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"spreadsheet monkey" wrote in
message ...
I tried this one, but it didnt work. this may be because I use excel 2003.
I
have had problems with this before. sometimes it helps to substitute the
(,)
with (;). although it didnt this time. I tried the other response, which
worked, so then I forgot about this one. in other words, it might work, I
just dont know how!

"Bob Phillips" wrote:

=SUBSTITUTE(SUBSTITUTE(MID(A2,3,99),"-","",1),"-","",1)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"spreadsheet monkey" wrote
in
message ...

I have a long list of numbers that I need to change.
the original number is:
1961-04-10-2691

I need it to be:
610410-2691

in other words, I need the first two numbers to go away and the first
two
minus signs (-) to go away.

can anyone help me?