Value manipulation complications

[Vic]

I need a new formula to add a 3rd condition to the first 2:

1. A1 is 00003056-021
I need B1 like this - if there are leading zeros then remove first 4 zeros
(only 4 and not 5). I need B1 to have 3056021 without the dash.

2. A1 is 30701267-042
I need B1 like this - if there are no leading zeros then remove digits
5-6-7-8 and the dash. I need b1 to have 3070042.

This formula does the first 2 condition:
=SUBSTITUTE(IF(LEFT(A1,4)="0000",RIGHT(A1,LEN(A1)-4),LEFT(A1,4) &

MID(A1,9,256)),"-","")

3. A1 is 1186-1067-188 (number with 2 dashes)
I need B1 like this - I need to pick up 1067 (4-digit number between 2
dashes) and 188, so the result will be 1067188.

Thank you.

 

[Luke M]

Should be able to just add another condition's to Gary's formula

=SUBSTITUTE(IF(ISNUMBER(FIND("-",A1,FIND("-",A1)+1)),MID(A1,FIND("-",A1)+1,256),IF(LEFT(A1,4)="0000",RIGHT(A1,LEN(A1)-4),LEFT(A1,4)
& MID(A1,9,256))),"-","")

 

[RagDyeR]

A bit shorter:

=IF(OR(LEFT(A1,4)="0000",MID(A1,5,1)="-"),LEFT(RIGHT(A1,8),4)&RIGHT(A1,3),LEFT(A1,4)&RIGHT(A1,3))

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I need a new formula to add a 3rd condition to the first 2:

1. A1 is 00003056-021
I need B1 like this - if there are leading zeros then remove first 4 zeros
(only 4 and not 5). I need B1 to have 3056021 without the dash.

2. A1 is 30701267-042
I need B1 like this - if there are no leading zeros then remove digits
5-6-7-8 and the dash. I need b1 to have 3070042.

This formula does the first 2 condition:
=SUBSTITUTE(IF(LEFT(A1,4)="0000",RIGHT(A1,LEN(A1)-4),LEFT(A1,4) &

MID(A1,9,256)),"-","")

3. A1 is 1186-1067-188 (number with 2 dashes)
I need B1 like this - I need to pick up 1067 (4-digit number between 2
dashes) and 188, so the result will be 1067188.

Thank you.

 

[Rick Rothstein]

Even shorter yet...

=MID(A1,1+4*(LEFT(A1,4)="0000"),4)&RIGHT(A1,3)

 

[Rick Rothstein]

Damn! I didn't see there was a condition 3. This is still a shorter formula (by about 25 characters and 4 function calls)...

=MID(A1,1+4*(LEFT(A1,4)="0000")+5*(COUNTIF(A1,"*-*-*")=1),4)&RIGHT(A1,3)

--
Rick (MVP - Excel)


Even shorter yet...

=MID(A1,1+4*(LEFT(A1,4)="0000"),4)&RIGHT(A1,3)

 

[Rick Rothstein]

Just in case you don't read into the sub-threads, here is the formula I posted there...

=MID(A1,1+4*(LEFT(A1,4)="0000")+5*(COUNTIF(A1,"*-*-*")=1),4)&RIGHT(A1,3)