Using Textjoin/Concat formulas to autofill a cell to create barcodes

%3CLINGO-SUB%20id%3D%22lingo-sub-1309582%22%20slang%3D%22en-US%22%3EUsing%20Textjpoin%2FConcat%20formulas%20to%20autofill%20a%20cell%20to%20create%20barcodes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1309582%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20hope%20you%20are%20all%20well%20and%20safe.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%20a%20formula%20to%20convert%20a%20sequential%20number%20list%20into%20barcodes%20(using%20installed%20Free%203%20of%209%20barcode%20font).%26nbsp%3B%20I%20have%20tried%20using%20both%20CONCAT%20and%20TEXTJOIN%26nbsp%3B%20functions%20without%20success%20an%20get%20error%20message.%26nbsp%3B%20The%26nbsp%3B%20format%20is%20as%26nbsp%3B%20per%20attached%2C%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20hugely%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20%26amp%3B%20Regards%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKevin%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1309582%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1309644%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Textjoin%2FConcat%20formulas%20to%20autofill%20a%20cell%20to%20create%20barcodes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1309644%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F622874%22%20target%3D%22_blank%22%3E%40KevinC2200%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20spreadsheet%20doesn't%20clearly%20show%20what%20your%20problem%20is%2C%20nor%20what%20you%20want%20the%20result%20to%20be.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20let%20me%20take%20a%20stab%20at%20what%20you%20might%20be%20needing%20in%20terms%20of%20functions.%20When%20numbers%20are%20involved%20in%20Concatenating%20or%20TEXTJOIN%20you%20need%20to%20convert%20the%20numbers%20into%20text%2C%20using%20the%20TEXT%20function.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20if%20you%20put%20%3CSTRONG%3E%3D%22*%22%26amp%3BTEXT(A6%2C%220000%22)%26amp%3B%22*%22%3C%2FSTRONG%3E%20into%20your%20cell%20B6%20it%20produces%20%3CSTRONG%3E*0101*%3C%2FSTRONG%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20%3CSTRONG%3E*101*%20%3C%2FSTRONG%3Eyou%20change%20the%20second%20argument%20in%20the%20TEXT%20function%20to%20read%20%22000%22%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1310520%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Textjoin%2FConcat%20formulas%20to%20autofill%20a%20cell%20to%20create%20barcodes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1310520%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F622874%22%20target%3D%22_blank%22%3E%40KevinC2200%3C%2FA%3E%26nbsp%3BPerhaps%20like%20in%20teh%20attached%20workbook%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1311814%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Textjoin%2FConcat%20formulas%20to%20autofill%20a%20cell%20to%20create%20barcodes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1311814%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20works%20perfectly%2C%20thank%20you%20very%20much%20for%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKevin%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1311826%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Textjoin%2FConcat%20formulas%20to%20autofill%20a%20cell%20to%20create%20barcodes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1311826%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Riny%2C%20thanks%20for%20the%20information%2C%20unfortunately%20when%20you%20change%26nbsp%3B%20number%20(e.g.%20A6)%26nbsp%3B%20I%20get%20a%20%23name%3F%20message%2C%20Mathete's%20solution%20worked%20perfectly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20once%20again%20for%20your%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKevin%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi, 

 

All hope you are all well and safe.

 

I am trying to use a formula to convert a sequential number list into barcodes (using installed Free 3 of 9 barcode font in destination cell).  I have tried using both CONCAT and TEXTJOIN  functions without success and continually get an error message.  The  format is as  per attached, follows:

 

Any help would be hugely appreciated.

 

Thanks & Regards, 

 

 

Kevin

4 Replies
Highlighted

@KevinC2200 

 

Your spreadsheet doesn't clearly show what your problem is, nor what you want the result to be.

 

So let me take a stab at what you might be needing in terms of functions. When numbers are involved in Concatenating or TEXTJOIN you need to convert the numbers into text, using the TEXT function.

 

So if you put ="*"&TEXT(A6,"0000")&"*" into your cell B6 it produces *0101* 

If you want *101* you change the second argument in the TEXT function to read "000"  

 

 

Highlighted

@KevinC2200 Perhaps like in teh attached workbook?

 

Highlighted

@mathetes 

 

That works perfectly, thank you very much for your help.

 

Regards, 

 

Kevin

Highlighted

@Riny_van_Eekelen 

 

Hi Riny, thanks for the information, unfortunately when you change  number (e.g. A6)  I get a #name? message, Mathete's solution worked perfectly.

 

Thank you once again for your help

 

Regards, 

 

 

Kevin