Please, help with concatenation
 

I have an excel spread sheet with data scanned from an pdf file. the scanned
data looks as follows:

Column A Column B
duration Comments
Row 1 1.5 Drill 8.5" hole
Row 2 from 4500'-46
Row 3 00 ft.

I am having to cut the data from Row 2, and 3 to tack it at the end of row
1. you can imagine that I have to do this for many files and for thousands of
rows. Is there a macro that can help me. I am novice to Excel macros, I need
something that can make this task easy, like highlighting cells 1,2,3 and
click a button to concatenate them the right way(in row 1B). Concatenate
Formula requires me to do as much work, since I have to change the cells
address.
Thank you
Al

Please, help with concatenation
 

Why you not use Access and the text capabilities that they have in SQL?
--
A friend


"Al" wrote:

I have an excel spread sheet with data scanned from an pdf file. the scanned
data looks as follows:

Column A Column B
duration Comments
Row 1 1.5 Drill 8.5" hole
Row 2 from 4500'-46
Row 3 00 ft.

I am having to cut the data from Row 2, and 3 to tack it at the end of row
1. you can imagine that I have to do this for many files and for thousands of
rows. Is there a macro that can help me. I am novice to Excel macros, I need
something that can make this task easy, like highlighting cells 1,2,3 and
click a button to concatenate them the right way(in row 1B). Concatenate
Formula requires me to do as much work, since I have to change the cells
address.
Thank you
Al

Please, help with concatenation
 

Such As?

"MMayor" wrote:

Why you not use Access and the text capabilities that they have in SQL?
--
A friend


"Al" wrote:

I have an excel spread sheet with data scanned from an pdf file. the scanned
data looks as follows:

Column A Column B
duration Comments
Row 1 1.5 Drill 8.5" hole
Row 2 from 4500'-46
Row 3 00 ft.

I am having to cut the data from Row 2, and 3 to tack it at the end of row
1. you can imagine that I have to do this for many files and for thousands of
rows. Is there a macro that can help me. I am novice to Excel macros, I need
something that can make this task easy, like highlighting cells 1,2,3 and
click a button to concatenate them the right way(in row 1B). Concatenate
Formula requires me to do as much work, since I have to change the cells
address.
Thank you
Al

Please, help with concatenation
 

One way:

Option Explicit
Sub testme()

Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = "" Then
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value & .Cells(iRow, "B").Value
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

But I think you may have a problem.

You'd probably want a space between "hole" and "from", but not between "-46" and
"00 ft".

This line did not add a space:
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value & .Cells(iRow, "B").Value

If you want a space, change it to:
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value & " " & .Cells(iRow, "B").Value

I think either way, you'll have some fixin' to do.


Al wrote:

I have an excel spread sheet with data scanned from an pdf file. the scanned
data looks as follows:

Column A Column B
duration Comments
Row 1 1.5 Drill 8.5" hole
Row 2 from 4500'-46
Row 3 00 ft.

I am having to cut the data from Row 2, and 3 to tack it at the end of row
1. you can imagine that I have to do this for many files and for thousands of
rows. Is there a macro that can help me. I am novice to Excel macros, I need
something that can make this task easy, like highlighting cells 1,2,3 and
click a button to concatenate them the right way(in row 1B). Concatenate
Formula requires me to do as much work, since I have to change the cells
address.
Thank you
Al

--

Dave Peterson

Please, help with concatenation
 

Its hard to tell exactly what gets copied to the clipboard but assuming
the first word should be in column A and the rest in column B try this.
It also assumes the delimiter is a space character. You may have to
experiment.

To run it simply go to your PDF document and copy the text to the
clipboard. Now go to Excel and assuming the active sheet is the right
one run the macro and the text will be placed in worksheet.

You can adjust the destination range accordingly.

Option Explicit

' Add reference to Microsoft Forms 2.0 Object Library

Sub Test()

Dim myDataObject As DataObject
Dim strText As String
Dim vArray As Variant
Dim i As Integer
Dim j As Integer

Set myDataObject = New DataObject

myDataObject.GetFromClipboard
strText = myDataObject.GetText(1)

vArray = Split(Expression:=strText, Delimiter:=" ",
Compa=vbTextCompare)

j = 0
strText = vbNullString
For i = LBound(vArray) To UBound(vArray)
If Len(Trim(vArray(i))) Then ' Check if empty string
If j = 0 Then
Range("A1").Offset(0, 0) = vArray(i)
j = j + 1
ElseIf j = 1 Then
strText = vArray(i)
j = j + 1
Else
strText = strText + " " + vArray(i)
End If
End If
Next
Range("A1").Offset(0, 1) = strText
Range("A1").Offset(0, 1).WrapText = False

Set myDataObject = Nothing

End Sub




*** Sent via Developersdex http://www.developersdex.com ***

Please, help with concatenation
 

thanks a million, I will try it.
Al

"Dave Peterson" wrote:

One way:

Option Explicit
Sub testme()

Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = "" Then
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value & .Cells(iRow, "B").Value
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

But I think you may have a problem.

You'd probably want a space between "hole" and "from", but not between "-46" and
"00 ft".

This line did not add a space:
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value & .Cells(iRow, "B").Value

If you want a space, change it to:
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value & " " & .Cells(iRow, "B").Value

I think either way, you'll have some fixin' to do.


Al wrote:

I have an excel spread sheet with data scanned from an pdf file. the scanned
data looks as follows:

Column A Column B
duration Comments
Row 1 1.5 Drill 8.5" hole
Row 2 from 4500'-46
Row 3 00 ft.

I am having to cut the data from Row 2, and 3 to tack it at the end of row
1. you can imagine that I have to do this for many files and for thousands of
rows. Is there a macro that can help me. I am novice to Excel macros, I need
something that can make this task easy, like highlighting cells 1,2,3 and
click a button to concatenate them the right way(in row 1B). Concatenate
Formula requires me to do as much work, since I have to change the cells
address.
Thank you
Al

--

Dave Peterson

Please, help with concatenation
 

Dave,
Just wanted to report that I tried it and it worked very nice. thank you
very much.It saved me hours of work.
Al

"Dave Peterson" wrote:

One way:

Option Explicit
Sub testme()

Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = "" Then
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value & .Cells(iRow, "B").Value
.Rows(iRow).Delete
End If
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

But I think you may have a problem.

You'd probably want a space between "hole" and "from", but not between "-46" and
"00 ft".

This line did not add a space:
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value & .Cells(iRow, "B").Value

If you want a space, change it to:
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value & " " & .Cells(iRow, "B").Value

I think either way, you'll have some fixin' to do.


Al wrote:

I have an excel spread sheet with data scanned from an pdf file. the scanned
data looks as follows:

Column A Column B
duration Comments
Row 1 1.5 Drill 8.5" hole
Row 2 from 4500'-46
Row 3 00 ft.

I am having to cut the data from Row 2, and 3 to tack it at the end of row
1. you can imagine that I have to do this for many files and for thousands of
rows. Is there a macro that can help me. I am novice to Excel macros, I need
something that can make this task easy, like highlighting cells 1,2,3 and
click a button to concatenate them the right way(in row 1B). Concatenate
Formula requires me to do as much work, since I have to change the cells
address.
Thank you
Al

--

Dave Peterson

Please, help with concatenation
 

Dear Edward, thank you very much. this looks very interesting. I have tried
what dave sent and it worked. I am going to try yours as well.
thanks Edward
Al

"Edward Ulle" wrote:

Its hard to tell exactly what gets copied to the clipboard but assuming
the first word should be in column A and the rest in column B try this.
It also assumes the delimiter is a space character. You may have to
experiment.

To run it simply go to your PDF document and copy the text to the
clipboard. Now go to Excel and assuming the active sheet is the right
one run the macro and the text will be placed in worksheet.

You can adjust the destination range accordingly.

Option Explicit

' Add reference to Microsoft Forms 2.0 Object Library

Sub Test()

Dim myDataObject As DataObject
Dim strText As String
Dim vArray As Variant
Dim i As Integer
Dim j As Integer

Set myDataObject = New DataObject

myDataObject.GetFromClipboard
strText = myDataObject.GetText(1)

vArray = Split(Expression:=strText, Delimiter:=" ",
Compa=vbTextCompare)

j = 0
strText = vbNullString
For i = LBound(vArray) To UBound(vArray)
If Len(Trim(vArray(i))) Then ' Check if empty string
If j = 0 Then
Range("A1").Offset(0, 0) = vArray(i)
j = j + 1
ElseIf j = 1 Then
strText = vArray(i)
j = j + 1
Else
strText = strText + " " + vArray(i)
End If
End If
Next
Range("A1").Offset(0, 1) = strText
Range("A1").Offset(0, 1).WrapText = False

Set myDataObject = Nothing

End Sub




*** Sent via Developersdex http://www.developersdex.com ***