| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Value manipulation complications
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Value manipulation complications
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))),"-","") -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Vic" wrote: 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Value manipulation complications
A bit shorter:
=IF(OR(LEFT(A1,4)="0000",MID(A1,5,1)="-"),LEFT(RIGHT(A1,8),4)&RIGHT(A1,3),LEFT(A1,4)&RIGH T(A1,3)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Vic" wrote in message ... 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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Value manipulation complications
Even shorter yet...
=MID(A1,1+4*(LEFT(A1,4)="0000"),4)&RIGHT(A1,3) -- Rick (MVP - Excel) "RagDyeR" wrote in message ... A bit shorter: =IF(OR(LEFT(A1,4)="0000",MID(A1,5,1)="-"),LEFT(RIGHT(A1,8),4)&RIGHT(A1,3),LEFT(A1,4)&RIGH T(A1,3)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Vic" wrote in message ... 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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Value manipulation complications
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) "Rick Rothstein" wrote in message ... Even shorter yet... =MID(A1,1+4*(LEFT(A1,4)="0000"),4)&RIGHT(A1,3) -- Rick (MVP - Excel) "RagDyeR" wrote in message ... A bit shorter: =IF(OR(LEFT(A1,4)="0000",MID(A1,5,1)="-"),LEFT(RIGHT(A1,8),4)&RIGHT(A1,3),LEFT(A1,4)&RIGH T(A1,3)) -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Vic" wrote in message ... 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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Value manipulation complications
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) -- Rick (MVP - Excel) "Vic" wrote in message ... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Value manipulation | Excel Discussion (Misc queries) | |||
| Complications putting text into multiple lines within a cell | Excel Discussion (Misc queries) | |||
| Complications with formulas | Excel Discussion (Misc queries) | |||
| sum complications | Excel Worksheet Functions | |||
| Complications Entering numbers in a cell | Excel Discussion (Misc queries) | |||
Hybrid Mode
