PDF import, rows?

[afc171]

Hi all,

I am importing a PDF file which imports exactly the way it is in the PDF file in to Excel, but how do I get it all to be on 1 row?

I need everything in column C so it looks as below, no spaces but with a comma after each MG (or sometimes UG)?

2x0.25mg,2x1mg,1x6mg

6x1mg

13x0.5mg,2x1.15mg

Thank you

 

[afc171]

It is line breaks, I replaced the Column with CTRL+J and comma so that is sorted now.

 

[MikeVol]

Maybe?

Option Explicit

Sub FormatColumnC()
    Dim cell        As Range

    Dim rng         As Range
    Set rng = ThisWorkbook.Worksheets("Sheet1") _
            .Range("C2:C" & ThisWorkbook.Worksheets("Sheet1") _
            .Cells(ThisWorkbook.Worksheets("Sheet1") _
            .Rows.Count, "C").End(xlUp).Row)    ' Replace Sheet1 with your sheet name
    Application.ScreenUpdating = False

    With CreateObject("VBScript.RegExp")
        .Global = True
        .IgnoreCase = True
        .Pattern = "(mg|ug)(\d)"

        For Each cell In rng

            If Not IsEmpty(cell.Value) Then
                cell.Value = .Replace(cell.Value, "$1,$2")
            End If

        Next cell

    End With

    Set rng = Nothing
    Application.ScreenUpdating = True
End Sub

 

[afc171]

Thanks Mike,

I think that just shows them instead of hidden...

 

[MikeVol]

            If Not IsEmpty(cell.Value) Then
                cell.Value = Replace(cell.Value, vbLf, "")
                cell.Value = .Replace(cell.Value, "$1,$2")
            End If

 

[hungtbatman1]

It is line breaks, I replaced the Column with CTRL+J and comma so that is sorted now.

line breaks?

Sub FormatAmount()
Dim lastRow As Long, cell_ As Range, rng As Range
    With ThisWorkbook.Worksheets("Sheet1")
        lastRow = .Range("C" & Rows.Count).End(xlUp).Row
        If lastRow = 1 Then Exit Sub    ' no data
        Set rng = .Range("C2:C" & lastRow)
    End With
    Application.ScreenUpdating = False
    For Each cell_ In rng
        cell_.Value = Replace(cell_.Value, vbLf, ",")
    Next cell_
    Application.ScreenUpdating = True
End Sub

 

[afc171]

Thanks guys