# Thread: dividing numbers/inserting spaces (Excel 2k)

1. ## dividing numbers/inserting spaces (Excel 2k)

Hi,

I need to perform a calculation on serial numbers (changing them into barcodes) I've got a formula in an Excelsheet, is there a way to export this into code?

Here is the formula:

=IF(B4<>0;CONCATENATE(" ";"(";CHAR(IF(VALUE(LEFT(B4;2))<50;VALUE(LEFT(B4;2 ))+48;VALUE(LEFT(B4;2))+142));CHAR(IF(VALUE(MID(B4 ;3;2))<50;VALUE(MID(B4;3;2))+48;VALUE(MID(B4;3;2)) +142));CHAR(IF(VALUE(MID(B4;5;2))<50;VALUE(MID(B4; 5;2))+48;VALUE(MID(B4;5;2))+142));CHAR(IF(VALUE(MI D(B4;7;2))<50;VALUE(MID(B4;7;2))+48;VALUE(MID(B4;7 ;2))+142));")";" ");"")

What it does is cut a number into pairs, perform a calculation on the pairs and place the result in a cell. The cell is then printed using a barcodefont

Any help will be much appreciated,

Paul

2. ## Re: dividing numbers/inserting spaces (Excel 2k)

Will valid numbers always have 8 digits? Or can the length vary?

3. ## Re: dividing numbers/inserting spaces (Excel 2k)

Does this function do what you want?

Function CalcBarcode(MyNum) As String
Dim n As Integer, i As Integer
If Val(MyNum) = 0 Then
CalcBarcode = ""
Else
CalcBarcode = " ("
For i = 1 To 4
n = Val(Mid(Format(MyNum, "00000000"), 2 * i - 1, 2)) + 48
If n >= 98 Then
n = n + 94
End If
CalcBarcode = CalcBarcode & Chr(n)
Next i
CalcBarcode = CalcBarcode & ") "
End If
End Function

Use it like this:

=CalcBarcode(B4)

4. ## Re: dividing numbers/inserting spaces (Excel 2k)

Hans,

Thanks again, it works of course. Last and final problem is that the numbers start with "0" (zero) and this zero gets trimmed away while importing the.csv-file using yet another piece of your code.

This seems to be a general Excel setting Is it possible to change this when when the code starts Excel?

Thanks,

Paul

5. ## Re: dividing numbers/inserting spaces (Excel 2k)

As in the thread starting at <post:=655,400>post 655,400</post:>, change the extension of the .csv file to .txt. The OpenText method has an optional argument FieldInfo that lets you specify how you want to treat columns. For example:

Workbooks.OpenText Filename:=strTxt, _
DataType:=xlDelimited, SemiColon:=True, Comma:=False, _
FieldInfo:=Array(Array(1, xlTextFormat), Array(2, xlGeneralFormat), Array(3, xlDMYFormat))

tells Excel to import column 1 as text, column 2 as general (i.e. let Excel decide) and column 3 as dates in DMY format.

6. ## Re: dividing numbers/inserting spaces (Excel 2k)

Hans,

Once again,

Thank you very, very much

Paul

7. ## Re: dividing numbers/inserting spaces (Excel 2k)

Hans,

Too soon, too soon. This doesn't work. Here's the code:
Workbooks.OpenText FileName:=strTxt, DataType:=xlDelimited, SemiColon:=True, Comma:=False, _
FieldInfo:=Array(Array(65, xlTextFormat), Array(66, xlTextFormat), Array(67, xlTextFormat))

It seems tom be ignored. To my frustration, Column 1 is in text format. (Without any settings to achieve this)
What is happening?

Paul

8. ## Re: dividing numbers/inserting spaces (Excel 2k)

Try specifying a complete array, starting with column 1, however tedious that is.

9. ## Re: dividing numbers/inserting spaces (Excel 2k)

Hans,

Well, it's not beautiful code, but it works. Apparently you have to enter a continuous array, in shameless contrast to what Excel help says about this option.

Thanks,

Paul

10. ## Re: dividing numbers/inserting spaces (Excel 2k)

Yes, the online help explicitly mentions that you can specify part of the columns and that they can be in any order, but it doesn't work that way... <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•