How to extract data from excel using VBA?

Question

Good day, please help me to extract data from columns of excel and make it a table format using VBA. I have copied and pasted data from PDF file but when converted to excel, data were transferred as one line/column in excel which should be separated. Please see sample below:
all data are in column A which details should be in column B.

data should be extracted like this:

Hope you understand and can help me with this. We are required to use VBA for this. Thank you!

Answer 1

Dear Eliza O. Riva

Thank you for submitting your question on the Q&A forum.

Based on the details you provided, I understand that you are seeking an optimal method to extract data from Excel according to your specific requirements. I have conducted research and tested a solution in my own test environment, and the results align precisely with what you are looking for.

Please follow the steps below to implement the solution:

1. Open your Excel file.
2. Press Alt + F11 to open the Visual Basic for Applications (VBA) editor.
3. Click on Insert → Module.
4. Paste the VBA code I have provided into the module window.
5. Press F5 to run the code.
6. Reopen your Excel file to verify the results.
   VBA Code:
   Sub SplitDataFromColumnA()    
   Dim i As Long    
   Dim arr As Variant    
   Dim lastRow As Long

lastRow = Cells(Rows.Count, "A").End(xlUp).Row    
For i = 1 To lastRow        
arr = Split(Cells(i, "A").Value, " ")        
If UBound(arr) >= 0 Then            
Cells(i, "B").Resize(1, UBound(arr) + 1).Value = arr        
End If    
Next i
End Sub

Kindly try these steps and let me know if the issue has been resolved. Should you encounter any further difficulties or require additional assistance, please do not hesitate to reach out. I am always available and happy to support you.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".      

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

Answer 2

Hi Eliza O. Riva,

I don't normally list videos, but I think in this instance it would help you rather than me typing it all out.

https://www.youtube.com/watch?v=p2304BjvrB8

Answer 3

I think the tool you are looking for is the Text to Columns function on the Data tab. The easiest way to use this function in VBA is to record a macro while you manually process a sample of your data. You can then use this recorded function call as a template. Tweak it as needed to process your actual data.