V
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) &
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.
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.