# Convert barcode

#### shimaa01234

##### Active Member
Before conversion 133600-133601 After the conversion, 133600133601660 Slash and the addition of 660 when be six figures and then Slash and then six figures put 660
Before conversion from 21769-21768 , after conversion 2176821769550 has been deleted "Slash" and the addition of 550 when the five numbers and then "Slash" and then five figures put 550
Before conversion 853 852 after conversion 853 852 600 has been deleted "Slash" and the addition of 600 when the six numbers only without the "Slash" put 600
Before conversion 852 200 after conversion 85220500 has been deleted "Slash" and the addition of 500 when the five numbers without the "Slash" put 500
 Before conversion After conversion 133600-133601 133600133601660 137960-137961 137960137961660 129820-129821 129820129821660 137917-137918 137917137918660 21768-21769 2176821769550 106062-106063 106062106063660 85392-85393 8539285393550 128780-128781 128780128781660 136220-136221 136220136221660 126231-126232 126231126232660 85218 85218500 85220 85220500

<colgroup><col><col></colgroup><tbody>
</tbody>
I wish the solution by the formulas

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### martindwilson

##### Well-known Member
try
=SUBSTITUTE(A1,"-","")&IF(LEN(A1)=13,"660","500")

#### shimaa01234

##### Active Member
Wonderful solution
There is a requirement of "550" when the number after the "Slash" and before five numbers

#### martindwilson

##### Well-known Member
=substitute(a1,"-","")&if(len(a1)=11,"550",if(len(a1)=13,"660","500"))

#### shimaa01234

##### Active Member
If the 6-digit only without the "Slash" is written 600

#### shimaa01234

##### Active Member
Like this
 Before conversion After conversion 133600-133601 133600133601660 21768-21769 2176821769550 853852 853852600 85220 85220500

<colgroup><col><col></colgroup><tbody>
</tbody>

#### Br0ck

##### Board Regular
This worked for me per your sample data...

Code:
``=SUBSTITUTE(A1,"-","")&IF(LEFT(A1,1)="1","660",IF(LEFT(A1,3)="853","600",IF(LEFT(A1,1)="2","550","500")))``

#### martindwilson

##### Well-known Member
any more requirements? like if its a Tuesday add 300. please lay out all your formats at once don't add new things piecemeal it's very annoying

#### martindwilson

##### Well-known Member
=substitute(a1,"-","")&if(len(a1)=11,"550",if(len(a1)=13,"660",if(len(a1)=6,"600","500")))
or maybe
=SUBSTITUTE(A1,"-","")&LOOKUP(LEN(A1),{5,6,11,13},{500,600,550,660})

Last edited:

#### Rick Rothstein

##### MrExcel MVP
=substitute(a1,"-","")&if(len(a1)=11,"550",if(len(a1)=13,"660",if(len(a1)=6,"600","500")))
or maybe
=SUBSTITUTE(A1,"-","")&LOOKUP(LEN(A1),{5,6,11,13},{500,600,550,660})

If the number of digits after the dash are always equal to the number of digits before the dash, then I would use your second formula above. However, if that is not the case, then perhaps this formula would work for the OP...

=IF(COUNTIF(A1,"*-*"),SUBSTITUTE(A1,"-","")&550+110*(FIND("-",A1)=7),A1&500)

1,191,002
Messages
5,984,100
Members
439,872
Latest member
noaman79

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back