Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

excel convert format issue

Hello all,

I have a big issue with an excel format, copied from the web.

In the excel spreadsheet, you will see that in sheet "Original", this is the web format when i paste in excel.

But know, i need to create a macro that will convert that like the example that i have in sheet " Convert report " just by clicking on the convert report button.

Is that something feasible?

How can i do this?

Thanks again for your help.

Classeur1.xlsm
0
Wilder1626
Asked:
Wilder1626
  • 4
  • 3
1 Solution
 
SiddharthRoutCommented:
>>>But know, i need to create a macro that will convert that like the example that i have in sheet " Convert report " just by clicking on the convert report button.

No a macro is not required :) But I can still make one of you want. If you want to do it manually then do this.

Select Col A and then

Excel 2003
Click on menu Data~~> Text To Columns~> Fixed Width. Click Next and then click Finish

Excel 2007/2010
Click on Data Tab~~> Text To Columns~> Fixed Width. Click Next and then click Finish

Hope this helps :)

Sid
0
 
Wilder1626Author Commented:
Hello SiddharthRout

Thanks for your help.

I was actually did manually but i would really love to have this has a macro if it's possible.

Thanks again.
0
 
SiddharthRoutCommented:
Ok Give me a short while

Sid
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Wilder1626Author Commented:
No problem.

Thanks again. Really appreciate
0
 
SiddharthRoutCommented:
Ok Try this file. Please note that the macro is specific to this file. In case the data format changes then you will have to amend the macro accordingly

After you open the file, simply run the macro Sub Sample(). The output will be generated in a new sheet called "Output"

HTH

Sid

Code Used

Sub Sample()
    Dim ws As Worksheet, ws1 As Worksheet
    Dim lastRow As Long, i As Long
    
    Set ws = Sheets("Original")
    
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Output").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    
    Set ws1 = Sheets.Add
    
    ws1.Name = "Output"
    
    ws.Cells.Copy ws1.Range("A1")
    
    ws1.Rows("1:2").Delete
    
    ws1.Columns("A:A").TextToColumns Destination:=ws1.Range("A1"), DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 1), Array(7, 1), Array(16, 1), Array(23, 1), Array(31, 1), _
    Array(39, 1), Array(47, 1), Array(57, 1), Array(66, 1), Array(74, 1), Array(83, 1)), _
    TrailingMinusNumbers:=True
    
    ws1.Columns("D:L").NumberFormat = "0.00"
    
    lastRow = ws1.Range("A" & Rows.Count).End(xlUp).Row
    
    For i = lastRow To 1 Step -1
        If InStr(1, ws1.Range("A" & i).Value, "---") Then
            ws1.Rows(i).Delete
        End If
    Next i
End Sub

Open in new window

Classeur1-4.xlsm
0
 
Wilder1626Author Commented:
OK, let me try this.

0
 
Wilder1626Author Commented:
Oh! this is so good.

Thank you so much.

I take it.

Thanks again for your help
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now