Hey, I am trying to transform numbers from column A (cells(i,1)) which are looked like 055778-1-012 into new numbers format: 5.57.78.012.

This task is new for me as I do not Applied user defined formation in VBA before so threfore I need some help for writing the Wright code.

I make effort to Express that is supposed to be my code but I am definetely experiencing problems with it. It simply does not Work. I am not aware of what I am missing. Could you help please?

Sub formatnumbers()

Dim i As Long, Finalcell As Variant

Range("A2").Select

Selection.End(xlDown).Select

Set Finalcell = Range("A2:" & Selection.Address)

For i = 3 To Finalcell

Cells(i, 1).Value = Format(55778 - 1 - 12, "##.##.##.000###")

Next iEnd sub

I ned this since each month I receive a data with numbers in one format but I have to transform those into other to use them in another manipulations.

Looks like excel does not like converting those types of strings, i suggest remove

all the "-" from your strings with ctrl+h, and run your macro again

Work this formula into your macro or use it directly in the worksheet: =MID(A1,2,1)&"."&MID(A1,3,2)&"."&MID(A1,5,2)&"."&RIGHT(A1,3)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Dear Derby, Thank you for your response.

I have just tried the functions that you suggested from the worksheet, and Excel states that I might check out for symbols. In short, it doesn't work from there.

Then I inserted the equation into my macro, I obtained a bizarre result, yet which has some repeating sequence for cells numbers (or my i-s variable numbers). Such as A3 gets "...3"; A4 - "...4"; A5 - "...5" and from A10 the cells shows "1...10". Anyway it is not the desired result.

Supposing that I'd like to transform only cells (1,1), whould the code look like below?

Sub formatchange()

Range("A1").Value = Mid(i, 2, 1) & "." & Mid(i, 3, 2) & "." & Mid(i, 5, 2) & "." & Right(i, 3)

End Sub

Finally, I also tested the suggested ctrl+h and then run my original code, which resulted in distribution like "5578112,...0000000". So it is not way to solve the issue.

Any other suggestions?

Please keep in mind that we can't see your worksheet from where we are sitting, nor do we know how your data got into the spreadsheet or how Excel is treating it. With that said: re: "

I have just tried the functions that you suggested from the worksheet, and Excel states that I might check out for symbols. In short, it doesn't work from there."I don't know what "Excel states that I might check out for symbols" means. All I can tell you is that with this exact string in A1...

055778-1-012

...the formula that I suggested returns this:

5.57.78.012

If there are leading or trailing spaces in your string or perhaps hidden characters, then my formula may not work. Spaces and hidden characters can sometimes occur when data is downloaded from an external application, such as a web page, etc.

re: "

Supposing that I'd like to transform only cells (1,1), whould the code look like below?"Again, the following will only work if you have the exact format shown below, with no leading spaces, trailing spaces or hidden characters.

055778-1-012

If you want to perform an operation on a variable, e.g. i, you need to assign a value to the variable.

Sub formatchange_v1() 'Assign cell value to variable i = Cells(1, 1) 'Build String and place in cell Cells(1, 1) = Mid(i, 2, 1) & "." & _ Mid(i, 3, 2) & "." & _ Mid(i, 5, 2) & "." & _ Right(i, 3) End SubYou don't really need to use a variable, but it makes the code a little easier to read. You can work directly on the value in the cell. This will work, but it looks a little messy:

Sub formatchange_v2() 'Build String and place in cell Cells(1, 1) = Mid(Cells(1, 1), 2, 1) & "." & _ Mid(Cells(1, 1), 3, 2) & "." & _ Mid(Cells(1, 1), 5, 2) & "." & _ Right(Cells(1, 1), 3) End Sub

To loop this through a range of cells in Column A, try this:Sub formatchange_v3() 'Determine last Row with data in Column A lastRw = Cells(Rows.Count, 1).End(xlUp).Row 'Loop through range For rw = 3 To lastRw 'Assign cell value to variable i = Cells(rw, 1) 'Build String and place in cell Cells(rw, 1) = Mid(i, 2, 1) & "." & _ Mid(i, 3, 2) & "." & _ Mid(i, 5, 2) & "." & _ Right(i, 3) Next End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you Derby it helped me.

The code for the cell (1,1) works perfectly - it transforms the value from 055778-1-012 into the desired 5.57.78.012.The code with looping throuth the rows - doesn't work. But as you provided a comprehensive explanations I slightly corrected your code and amazingly it returns the required values. I couldn't really have come up with this code, thank you again.

I modified your code since my vba marked the area with i = Cells(rw, 1) and stated that I should start debugging this part. Don't know why.

Sub formatchange_v1()

Dim i As Long

'Assign cell value to variable

For i = 1 To 138

'Here I know the number of the given cells but it is usefull to apply count.rows as you had shownCells(i, 2) = Mid(Cells(i, 1), 2, 1) & "." & _

Mid(Cells(i, 1), 3, 2) & "." & _

Mid(Cells(i, 1), 5, 2) & "." & _

Right(Cells(i, 1), 3)

Next iEnd Sub

Or as you suggested before:

Sub formatchange_v3()

Dim lastRw As Integer, i As Integer

Dim rw As Integer'Determine last Row with data in Column A

lastRw = Cells(Rows.Count, 1).End(xlUp).Row

'Loop through range

For rw = 1 To lastRw

'Assign cell value to variable

'i = Cells(rw, 1)

'Build String and place in cell

Cells(rw, 4) = Mid(Cells(rw, 1), 2, 1) & "." & _

Mid(Cells(rw, 1), 3, 2) & "." & _

Mid(Cells(rw, 1), 5, 2) & "." & _

Right(Cells(rw, 1), 3)

Next

End Sub

I'll go along with the fact that you "modified" my code, but "corrected" is not the right term. It worked just fine as I described.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll