Concatenate formula

**Jokacave** · 01-31-2009, 06:33 AM

Hi,
I try change code numbers like the follow one
0101010100
in this
01-01-01-01-00
and I know that with this formula

Please Login or Register  to view this content.

I can work it out.
So I create the following code

Please Login or Register  to view this content.

but doenst work.
Can somebody help me.
Thanks in advance

**VBA Noob** · 01-31-2009, 06:38 AM

Would this do what you want

Please Login or Register  to view this content.

VBA Noob

**DonkeyOte** · 01-31-2009, 06:46 AM

Or perhaps something along the lines of:

Please Login or Register  to view this content.

So if A1 holds your text string then

B1: =SPLITTEXT(A1,2,"-")

that is to say split the text every 2 chars by insertion of delimiter "-"

**Jokacave** · 01-31-2009, 08:11 AM

Thanks both VBA Noob & DonkeyOte
But again cannot work it out...
I attached the files maybe will help you
*First I use the "TesteAccao" to open the one to work that in this case is the "testnovo"

**DonkeyOte** · 01-31-2009, 09:27 AM

Jokacave, if you want to have the formula calling the UDF stored in each cell in testnovo.xls you would really need to have the function stored in testnovo.xls itself rather than testaccao.xls.

If you just wish to return the results as static values there are other options open to you... eg:

Please Login or Register  to view this content.

I would make the point that all of your routines in TesteAccao.xls should reside in Module1 ... not ThisWorkbook... ThisWorkbook should really be reserved for Workbook level events.

I've not reviewed your code in full but have altered a few pieces in the above... note I moved all of your Declarations to the top of the Module (Dim) and also altered the Integer types to Long... Integer in 32-bit VBA is converted to Long so it makes sense to simply use Long (if the likes of Byte not guaranteed to be viable)... I also changed the InputBox to Application.InputBox which allows you to add a Type which will restrict data entry to viable types (ie 1 = number) ... you should add further handlers to account for invalid number being entered etc... as I say not complete code but hopefully enough to point you in the right direction.

**DonkeyOte** · 01-31-2009, 09:42 AM

I should have added that if your strings are of constant length across any/all files against which you want to apply this format (ie 10 chars etc...) you can just use a Custom Format as provided by VBANoob... I was under the impression that this may not be the case (hence the UDF)... if not use a Format (the UDF is overkill).