Convert barcode

shimaa01234

Active Member
Joined
Jun 24, 2014
Messages
446
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 conversionAfter conversion
133600-133601133600133601660
137960-137961137960137961660
129820-129821129820129821660
137917-137918137917137918660
21768-217692176821769550
106062-106063106062106063660
85392-853938539285393550
128780-128781128780128781660
136220-136221136220136221660
126231-126232126231126232660
8521885218500
8522085220500

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

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Wonderful solution
There is a requirement of "550" when the number after the "Slash" and before five numbers
 
Upvote 0
Like this
Before conversionAfter conversion
133600-133601133600133601660
21768-217692176821769550
853852853852600
8522085220500

<colgroup><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
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")))
 
Upvote 0
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
 
Upvote 0
=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:
Upvote 0
=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)
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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?

Disable AdBlock

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
Back
Top