Hi,

I have a report that is used every day to key in information from. It
would be a lot simpler just to parse out the needed data. Here is the
detail...

I imported the data file into Excel, and all I need is 4 numbers from
certain cells. When imported the numbers are in one continuous line
(in a single cell) with breaks in between them like this (ignore the
quotation marks):

Cell A12

" 74,338.00 40,000.00
1,500.00" etc.

I need something simple that will read the data in this cell and grab
the first four numbers and assign them to different cells in a
spreadsheet. So 74,338.00 goes to Cell B5, 40,000.00 to B6, etc.

Thanks in advance, the members of this board are always very helpful!

This'll pick out the first 4 numbers (if there are 4) and plop them into B5, B6,
....

Option Explicit
Sub testme()
Dim myCell As Range
Dim DestCell As Range
Dim myArr As Variant
Dim iCtr As Long
Dim nCtr As Long

Set myCell = ActiveSheet.Range("a12")
myArr = Split(Application.Trim(myCell.Value), " ")

Set DestCell = ActiveSheet.Range("B5")
nCtr = 0
For iCtr = LBound(myArr) To UBound(myArr)
If IsNumeric(myArr(iCtr)) Then
DestCell.Value = myArr(iCtr)
Set DestCell = DestCell.Offset(1, 0)
nCtr = nCtr + 1
If nCtr = 4 Then
Exit For
End If
End If
Next iCtr

End Sub

But it uses VBA's Split command. That was added in xl2k. If you run xl97, this
version won't work.

But there is a simple fix.



wrote:

Hi,

I have a report that is used every day to key in information from. It
would be a lot simpler just to parse out the needed data. Here is the
detail...

I imported the data file into Excel, and all I need is 4 numbers from
certain cells. When imported the numbers are in one continuous line
(in a single cell) with breaks in between them like this (ignore the
quotation marks):

Cell A12

" 74,338.00 40,000.00
1,500.00" etc.

I need something simple that will read the data in this cell and grab
the first four numbers and assign them to different cells in a
spreadsheet. So 74,338.00 goes to Cell B5, 40,000.00 to B6, etc.

Thanks in advance, the members of this board are always very helpful!

--

Dave Peterson

Here's Daves code with some of the white space removed:

Sub abc()
Dim s As String, v As Variant
s = Range("A12").Value
v = Split(Application.Trim(s), " ")
Range("B5").Resize(4, 1) = Application.Transpose(v)
End Sub

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote in message
...
This'll pick out the first 4 numbers (if there are 4) and plop them into
B5, B6,
...

Option Explicit
Sub testme()
Dim myCell As Range
Dim DestCell As Range
Dim myArr As Variant
Dim iCtr As Long
Dim nCtr As Long

Set myCell = ActiveSheet.Range("a12")
myArr = Split(Application.Trim(myCell.Value), " ")

Set DestCell = ActiveSheet.Range("B5")
nCtr = 0
For iCtr = LBound(myArr) To UBound(myArr)
If IsNumeric(myArr(iCtr)) Then
DestCell.Value = myArr(iCtr)
Set DestCell = DestCell.Offset(1, 0)
nCtr = nCtr + 1
If nCtr = 4 Then
Exit For
End If
End If
Next iCtr

End Sub

But it uses VBA's Split command. That was added in xl2k. If you run
xl97, this
version won't work.

But there is a simple fix.



wrote:

Hi,

I have a report that is used every day to key in information from. It
would be a lot simpler just to parse out the needed data. Here is the
detail...

I imported the data file into Excel, and all I need is 4 numbers from
certain cells. When imported the numbers are in one continuous line
(in a single cell) with breaks in between them like this (ignore the
quotation marks):

Cell A12

" 74,338.00 40,000.00
1,500.00" etc.

I need something simple that will read the data in this cell and grab
the first four numbers and assign them to different cells in a
spreadsheet. So 74,338.00 goes to Cell B5, 40,000.00 to B6, etc.

Thanks in advance, the members of this board are always very helpful!

--

Dave Peterson

You did remove a few checks, though <bg.

I wasn't sure if they were necessary.

Tom Ogilvy wrote:

Here's Daves code with some of the white space removed:

Sub abc()
Dim s As String, v As Variant
s = Range("A12").Value
v = Split(Application.Trim(s), " ")
Range("B5").Resize(4, 1) = Application.Transpose(v)
End Sub

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
This'll pick out the first 4 numbers (if there are 4) and plop them into
B5, B6,
...

Option Explicit
Sub testme()
Dim myCell As Range
Dim DestCell As Range
Dim myArr As Variant
Dim iCtr As Long
Dim nCtr As Long

Set myCell = ActiveSheet.Range("a12")
myArr = Split(Application.Trim(myCell.Value), " ")

Set DestCell = ActiveSheet.Range("B5")
nCtr = 0
For iCtr = LBound(myArr) To UBound(myArr)
If IsNumeric(myArr(iCtr)) Then
DestCell.Value = myArr(iCtr)
Set DestCell = DestCell.Offset(1, 0)
nCtr = nCtr + 1
If nCtr = 4 Then
Exit For
End If
End If
Next iCtr

End Sub

But it uses VBA's Split command. That was added in xl2k. If you run
xl97, this
version won't work.

But there is a simple fix.



wrote:

Hi,

I have a report that is used every day to key in information from. It
would be a lot simpler just to parse out the needed data. Here is the
detail...

I imported the data file into Excel, and all I need is 4 numbers from
certain cells. When imported the numbers are in one continuous line
(in a single cell) with breaks in between them like this (ignore the
quotation marks):

Cell A12

" 74,338.00 40,000.00
1,500.00" etc.

I need something simple that will read the data in this cell and grab
the first four numbers and assign them to different cells in a
spreadsheet. So 74,338.00 goes to Cell B5, 40,000.00 to B6, etc.

Thanks in advance, the members of this board are always very helpful!

--

Dave Peterson

--

Dave Peterson

if there are no interspersed text entries and there are four numbers, then
the cut down version seems to work fine.

--
Regards,
Tom Ogilvy


"Dave Peterson" wrote in message
...
You did remove a few checks, though <bg.

I wasn't sure if they were necessary.

Tom Ogilvy wrote:

Here's Daves code with some of the white space removed:

Sub abc()
Dim s As String, v As Variant
s = Range("A12").Value
v = Split(Application.Trim(s), " ")
Range("B5").Resize(4, 1) = Application.Transpose(v)
End Sub

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
This'll pick out the first 4 numbers (if there are 4) and plop them
into
B5, B6,
...

Option Explicit
Sub testme()
Dim myCell As Range
Dim DestCell As Range
Dim myArr As Variant
Dim iCtr As Long
Dim nCtr As Long

Set myCell = ActiveSheet.Range("a12")
myArr = Split(Application.Trim(myCell.Value), " ")

Set DestCell = ActiveSheet.Range("B5")
nCtr = 0
For iCtr = LBound(myArr) To UBound(myArr)
If IsNumeric(myArr(iCtr)) Then
DestCell.Value = myArr(iCtr)
Set DestCell = DestCell.Offset(1, 0)
nCtr = nCtr + 1
If nCtr = 4 Then
Exit For
End If
End If
Next iCtr

End Sub

But it uses VBA's Split command. That was added in xl2k. If you run
xl97, this
version won't work.

But there is a simple fix.



wrote:

Hi,

I have a report that is used every day to key in information from. It
would be a lot simpler just to parse out the needed data. Here is the
detail...

I imported the data file into Excel, and all I need is 4 numbers from
certain cells. When imported the numbers are in one continuous line
(in a single cell) with breaks in between them like this (ignore the
quotation marks):

Cell A12

" 74,338.00 40,000.00
1,500.00" etc.

I need something simple that will read the data in this cell and grab
the first four numbers and assign them to different cells in a
spreadsheet. So 74,338.00 goes to Cell B5, 40,000.00 to B6, etc.

Thanks in advance, the members of this board are always very helpful!

--

Dave Peterson

--

Dave Peterson

Yep. I wasn't sure that this would always be the case.

Grabbing the first 4 numbers just didn't see like a good enough spec to me.



Tom Ogilvy wrote:

if there are no interspersed text entries and there are four numbers, then
the cut down version seems to work fine.

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
You did remove a few checks, though <bg.

I wasn't sure if they were necessary.

Tom Ogilvy wrote:

Here's Daves code with some of the white space removed:

Sub abc()
Dim s As String, v As Variant
s = Range("A12").Value
v = Split(Application.Trim(s), " ")
Range("B5").Resize(4, 1) = Application.Transpose(v)
End Sub

--
Regards,
Tom Ogilvy

"Dave Peterson" wrote in message
...
This'll pick out the first 4 numbers (if there are 4) and plop them
into
B5, B6,
...

Option Explicit
Sub testme()
Dim myCell As Range
Dim DestCell As Range
Dim myArr As Variant
Dim iCtr As Long
Dim nCtr As Long

Set myCell = ActiveSheet.Range("a12")
myArr = Split(Application.Trim(myCell.Value), " ")

Set DestCell = ActiveSheet.Range("B5")
nCtr = 0
For iCtr = LBound(myArr) To UBound(myArr)
If IsNumeric(myArr(iCtr)) Then
DestCell.Value = myArr(iCtr)
Set DestCell = DestCell.Offset(1, 0)
nCtr = nCtr + 1
If nCtr = 4 Then
Exit For
End If
End If
Next iCtr

End Sub

But it uses VBA's Split command. That was added in xl2k. If you run
xl97, this
version won't work.

But there is a simple fix.



wrote:

Hi,

I have a report that is used every day to key in information from. It
would be a lot simpler just to parse out the needed data. Here is the
detail...

I imported the data file into Excel, and all I need is 4 numbers from
certain cells. When imported the numbers are in one continuous line
(in a single cell) with breaks in between them like this (ignore the
quotation marks):

Cell A12

" 74,338.00 40,000.00
1,500.00" etc.

I need something simple that will read the data in this cell and grab
the first four numbers and assign them to different cells in a
spreadsheet. So 74,338.00 goes to Cell B5, 40,000.00 to B6, etc.

Thanks in advance, the members of this board are always very helpful!

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Hi Guys,

Thanks for the ideas, I will try them now. Sorry about not being
precise enough, I'm still learning. There is no data whatsoever
between the numbers.

Dave,

Your code worked like a charm. I made two small mistakes which I'm
sure will be easy to correct.

When I set it up I forget to say I wanted the data in B5, C5, D5, E5
instead of B5, B6, B7, B8.

Also I need to run this identical routine 7 times on 7 cell addresses.

Thanks again, you guys are terrific...

I guess there is no way to edit after you have posted on here :(.

One other correction and that is it...

This routine needs to be run 49 (not 7) times using data from 49 cells.

So you want to look at A12:A60 (49 cells) and put them in B5:E5, B6:E6, ...

If yes, then still with all the (un!)necessary checks:

Option Explicit
Sub testme()
Dim myRng As Range
Dim myCell As Range
Dim DestCell As Range
Dim TopDest As Range
Dim myArr As Variant
Dim iCtr As Long
Dim nCtr As Long

Set myRng = ActiveSheet.Range("a12:a60") 'about 49 cells
Set TopDest = ActiveSheet.Range("B5")

For Each myCell In myRng.Cells
Set DestCell = TopDest
myArr = Split(Application.Trim(myCell.Value), " ")
nCtr = 0
For iCtr = LBound(myArr) To UBound(myArr)
If IsNumeric(myArr(iCtr)) Then
DestCell.Value = myArr(iCtr)
Set DestCell = DestCell.Offset(0, 1)
nCtr = nCtr + 1
If nCtr = 4 Then
Exit For
End If
End If
Next iCtr
Set TopDest = TopDest.Offset(1, 0)
Next myCell

End Sub

====
Without those validity checks, you could modify Tom's code like:

Option Explicit
Sub abc()
Dim s As String
Dim v As Variant
Dim myCell As Range
Dim myRng As Range
Dim DestCell As Range

With ActiveSheet
Set myRng = .Range("a12:a60")
Set DestCell = .Range("B5")
End With

For Each myCell In myRng.Cells
s = myCell.Value
v = Split(Application.Trim(s), " ")
DestCell.Resize(1, 4) = v
Set DestCell = DestCell.Offset(1, 0)
Next myCell

End Sub

(but I like white space!)



wrote:

Dave,

Your code worked like a charm. I made two small mistakes which I'm
sure will be easy to correct.

When I set it up I forget to say I wanted the data in B5, C5, D5, E5
instead of B5, B6, B7, B8.

Also I need to run this identical routine 7 times on 7 cell addresses.

Thanks again, you guys are terrific...

--

Dave Peterson

Dave,

I see the need for precision when specifying what is required,
hopefully this is the last mistake I will make.

I was able to figure out how to reorder them so the numbers generated
across instead of down, anyway to the final question..

The cells I am pulling the information from are not continguous, they
are like b19, b22, b25 and so on but then at b67 it jumps next to b87.
The reason is because I'm pulling an electronic copy of a printed
report so there are pages with headings etc. that cause gaps.

Would it be easier to create a named range in Excel for the numbers or
just put in a string?

Thanks AGAIN!

PS - Tom's code looked really elegant, I just jumped on yours first
because I'm not advanced enough yet to understand how he did it. I
can't wait to get better at this stuff so I can help people as well, it
is hard to find good study material, one friend recommended college
books as the ones in bookstores aren't comprehensive enough.

I'm not sure what would be best.

If the headings are in column A, so that only the cells with stuff in them (in
column B) are the cells you want to process, then you could use:

Set myRng = Nothing
On Error Resume Next
'large enough to get all, but not anything more
Set myRng = ActiveSheet.Range("b19:b999") _
.Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "nothing found"
Exit Sub
End If

======

If you see a pattern for the row numbers, you could rely on that, too. But I'm
afraid to guess how your rows increment--"like" scares me. And I'm not sure how
groups of 17 would result in 49 cells to process.

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with.

And Tom's code is much more straight forward. But if your data is not as clean
as you say, it may not do what you want. But if the data is gonna always be
clean, my code does way too much work.


wrote:

Dave,

I see the need for precision when specifying what is required,
hopefully this is the last mistake I will make.

I was able to figure out how to reorder them so the numbers generated
across instead of down, anyway to the final question..

The cells I am pulling the information from are not continguous, they
are like b19, b22, b25 and so on but then at b67 it jumps next to b87.
The reason is because I'm pulling an electronic copy of a printed
report so there are pages with headings etc. that cause gaps.

Would it be easier to create a named range in Excel for the numbers or
just put in a string?

Thanks AGAIN!

PS - Tom's code looked really elegant, I just jumped on yours first
because I'm not advanced enough yet to understand how he did it. I
can't wait to get better at this stuff so I can help people as well, it
is hard to find good study material, one friend recommended college
books as the ones in bookstores aren't comprehensive enough.

--

Dave Peterson

Dave,

Thanks again, the data will be clean. I got it to work in a clumsy
patchwork fashion (I used to program a long time ago and am getting
reacquainted as I forgot almost everything lol).

For some reason even though the named range had 44 cells, it would
always stop at 35 in the output so I did two command buttons working on
two named ranges (I didn't know how to combine the code). The data is
then sent to range B130-B173 and referenced in the cells it really
needs to go into (same as output range below).

I'll put the code down below, but first will list the ranges (input
range is what I am using now, output range is currently B130-B173 but
should be what is below). I don't know if it will be readable but it
goes like this, B211 input, B5 Output, C211 Input, C5 Output, etc.)

B 211 C 211 D 211 E 211 B 5 C 5 D 5 E 5
B 214 C 214 D 214 E 214 B 6 C 6 D 6 E 6
B 217 C 217 D 217 E 217 B 7 C 7 D 7 E 7
B 220 C 220 D 220 E 220 B 8 C 8 D 8 E 8
B 223 C 223 D 223 E 223 B 9 C 9 D 9 E 9
B 226 C 226 D 226 E 226 B 10 C 10 D 10 E 10
B 229 C 229 D 229 E 229 B 11 C 11 D 11 E 11
B 232 C 232 D 232 E 232 B 12 C 12 D 12 E 12
B 244 C 244 D 244 E 244 B 22 C 22 D 22 E 22
B 247 C 247 D 247 E 247 B 23 C 23 D 23 E 23
B 250 C 250 D 250 E 250 B 24 C 24 D 24 E 24
B 253 C 253 D 253 E 253 B 25 C 25 D 25 E 25
B 256 C 256 D 256 E 256 B 26 C 26 D 26 E 26
B 259 C 259 D 259 E 259 B 27 C 27 D 27 E 27
B 276 C 276 D 276 E 276 B 37 C 37 D 37 E 37
B 279 C 279 D 279 E 279 B 38 C 38 D 38 E 38
B 282 C 282 D 282 E 282 B 39 C 39 D 39 E 39
B 285 C 285 D 285 E 285 B 40 C 40 D 40 E 40
B 288 C 288 D 288 E 288 B 41 C 41 D 41 E 41
B 291 C 291 D 291 E 291 B 42 C 42 D 42 E 42
B 294 C 294 D 294 E 294 B 43 C 43 D 43 E 43
B 300 C 300 D 300 E 300 B 53 C 53 D 53 E 53
B 303 C 303 D 303 E 303 B 54 C 54 D 54 E 54
B 306 C 306 D 306 E 306 B 55 C 55 D 55 E 55
B 309 C 309 D 309 E 309 B 56 C 56 D 56 E 56
B 312 C 312 D 312 E 312 B 57 C 57 D 57 E 57
B 315 C 315 D 315 E 315 B 58 C 58 D 58 E 58
B 318 C 318 D 318 E 318 B 68 C 68 D 68 E 68
B 321 C 321 D 321 E 321 B 69 C 69 D 69 E 69
B 324 C 324 D 324 E 324 B 70 C 70 D 70 E 70
B 327 C 327 D 327 E 327 B 71 C 71 D 71 E 71
B 344 C 344 D 344 E 344 B 72 C 72 D 72 E 72
B 347 C 347 D 347 E 347 B 73 C 73 D 73 E 73
B 356 C 356 D 356 E 356 B 83 C 83 D 83 E 83
B 359 C 359 D 359 E 359 B 84 C 84 D 84 E 84
B 362 C 362 D 362 E 362 B 85 C 85 D 85 E 85
B 365 C 365 D 365 E 365 B 86 C 86 D 86 E 86
B 368 C 368 D 368 E 368 B 87 C 87 D 87 E 87
B 371 C 371 D 371 E 371 B 100 C 100 D 100 E 100
B 374 C 374 D 374 E 374 B 101 C 101 D 101 E 101
B 377 C 377 D 377 E 377 B 102 C 102 D 102 E 102
B 380 C 380 D 380 E 380 B 103 C 103 D 103 E 103
B 383 C 383 D 383 E 383 B 104 C 104 D 104 E 104
B 386 C 386 D 386 E 386 B 105 C 105 D 105 E 105

Here is the code:

Private Sub CommandButton1_Click()

Dim myRng As Range
Dim myCell As Range
Dim DestCell As Range
Dim TopDest As Range
Dim myArr As Variant
Dim iCtr As Long
Dim nCtr As Long



Set myRng = ActiveSheet.Range("Tellers")
Set TopDest = ActiveSheet.Range("B130")


For Each myCell In myRng.Cells
Set DestCell = TopDest
myArr = Split(Application.Trim(myCell.Value), " ")
nCtr = 0
For iCtr = LBound(myArr) To UBound(myArr)
If IsNumeric(myArr(iCtr)) Then
DestCell.Value = myArr(iCtr)
Set DestCell = DestCell.Offset(0, 1)
nCtr = nCtr + 1
If nCtr = 4 Then
Exit For
End If
End If
Next iCtr
Set TopDest = TopDest.Offset(1, 0)
Next myCell



End Sub

Private Sub CommandButton2_Click()
Dim myRng As Range
Dim myCell As Range
Dim DestCell As Range
Dim TopDest As Range
Dim myArr As Variant
Dim iCtr As Long
Dim nCtr As Long



Set myRng = ActiveSheet.Range("Tellersa")
Set TopDest = ActiveSheet.Range("B162")


For Each myCell In myRng.Cells
Set DestCell = TopDest
myArr = Split(Application.Trim(myCell.Value), " ")
nCtr = 0
For iCtr = LBound(myArr) To UBound(myArr)
If IsNumeric(myArr(iCtr)) Then
DestCell.Value = myArr(iCtr)
Set DestCell = DestCell.Offset(0, 1)
nCtr = nCtr + 1
If nCtr = 4 Then
Exit For
End If
End If
Next iCtr
Set TopDest = TopDest.Offset(1, 0)
Next myCell
End Sub

Excel limits the number of characters in the address of name (in the refers to
box) in the insert|Name|define dialog.

But you could modify your code

with activesheet
Set myRng = union(.Range("Tellers"),.range("tellersa"))
end with

I'm not sure how that changes the destination cells, though.


wrote:

Dave,

Thanks again, the data will be clean. I got it to work in a clumsy
patchwork fashion (I used to program a long time ago and am getting
reacquainted as I forgot almost everything lol).

For some reason even though the named range had 44 cells, it would
always stop at 35 in the output so I did two command buttons working on
two named ranges (I didn't know how to combine the code). The data is
then sent to range B130-B173 and referenced in the cells it really
needs to go into (same as output range below).

I'll put the code down below, but first will list the ranges (input
range is what I am using now, output range is currently B130-B173 but
should be what is below). I don't know if it will be readable but it
goes like this, B211 input, B5 Output, C211 Input, C5 Output, etc.)

B 211 C 211 D 211 E 211 B 5 C 5 D 5 E 5
B 214 C 214 D 214 E 214 B 6 C 6 D 6 E 6
B 217 C 217 D 217 E 217 B 7 C 7 D 7 E 7
B 220 C 220 D 220 E 220 B 8 C 8 D 8 E 8
B 223 C 223 D 223 E 223 B 9 C 9 D 9 E 9
B 226 C 226 D 226 E 226 B 10 C 10 D 10 E 10
B 229 C 229 D 229 E 229 B 11 C 11 D 11 E 11
B 232 C 232 D 232 E 232 B 12 C 12 D 12 E 12
B 244 C 244 D 244 E 244 B 22 C 22 D 22 E 22
B 247 C 247 D 247 E 247 B 23 C 23 D 23 E 23
B 250 C 250 D 250 E 250 B 24 C 24 D 24 E 24
B 253 C 253 D 253 E 253 B 25 C 25 D 25 E 25
B 256 C 256 D 256 E 256 B 26 C 26 D 26 E 26
B 259 C 259 D 259 E 259 B 27 C 27 D 27 E 27
B 276 C 276 D 276 E 276 B 37 C 37 D 37 E 37
B 279 C 279 D 279 E 279 B 38 C 38 D 38 E 38
B 282 C 282 D 282 E 282 B 39 C 39 D 39 E 39
B 285 C 285 D 285 E 285 B 40 C 40 D 40 E 40
B 288 C 288 D 288 E 288 B 41 C 41 D 41 E 41
B 291 C 291 D 291 E 291 B 42 C 42 D 42 E 42
B 294 C 294 D 294 E 294 B 43 C 43 D 43 E 43
B 300 C 300 D 300 E 300 B 53 C 53 D 53 E 53
B 303 C 303 D 303 E 303 B 54 C 54 D 54 E 54
B 306 C 306 D 306 E 306 B 55 C 55 D 55 E 55
B 309 C 309 D 309 E 309 B 56 C 56 D 56 E 56
B 312 C 312 D 312 E 312 B 57 C 57 D 57 E 57
B 315 C 315 D 315 E 315 B 58 C 58 D 58 E 58
B 318 C 318 D 318 E 318 B 68 C 68 D 68 E 68
B 321 C 321 D 321 E 321 B 69 C 69 D 69 E 69
B 324 C 324 D 324 E 324 B 70 C 70 D 70 E 70
B 327 C 327 D 327 E 327 B 71 C 71 D 71 E 71
B 344 C 344 D 344 E 344 B 72 C 72 D 72 E 72
B 347 C 347 D 347 E 347 B 73 C 73 D 73 E 73
B 356 C 356 D 356 E 356 B 83 C 83 D 83 E 83
B 359 C 359 D 359 E 359 B 84 C 84 D 84 E 84
B 362 C 362 D 362 E 362 B 85 C 85 D 85 E 85
B 365 C 365 D 365 E 365 B 86 C 86 D 86 E 86
B 368 C 368 D 368 E 368 B 87 C 87 D 87 E 87
B 371 C 371 D 371 E 371 B 100 C 100 D 100 E 100
B 374 C 374 D 374 E 374 B 101 C 101 D 101 E 101
B 377 C 377 D 377 E 377 B 102 C 102 D 102 E 102
B 380 C 380 D 380 E 380 B 103 C 103 D 103 E 103
B 383 C 383 D 383 E 383 B 104 C 104 D 104 E 104
B 386 C 386 D 386 E 386 B 105 C 105 D 105 E 105

Here is the code:

Private Sub CommandButton1_Click()

Dim myRng As Range
Dim myCell As Range
Dim DestCell As Range
Dim TopDest As Range
Dim myArr As Variant
Dim iCtr As Long
Dim nCtr As Long

Set myRng = ActiveSheet.Range("Tellers")
Set TopDest = ActiveSheet.Range("B130")

For Each myCell In myRng.Cells
Set DestCell = TopDest
myArr = Split(Application.Trim(myCell.Value), " ")
nCtr = 0
For iCtr = LBound(myArr) To UBound(myArr)
If IsNumeric(myArr(iCtr)) Then
DestCell.Value = myArr(iCtr)
Set DestCell = DestCell.Offset(0, 1)
nCtr = nCtr + 1
If nCtr = 4 Then
Exit For
End If
End If
Next iCtr
Set TopDest = TopDest.Offset(1, 0)
Next myCell

End Sub

Private Sub CommandButton2_Click()
Dim myRng As Range
Dim myCell As Range
Dim DestCell As Range
Dim TopDest As Range
Dim myArr As Variant
Dim iCtr As Long
Dim nCtr As Long

Set myRng = ActiveSheet.Range("Tellersa")
Set TopDest = ActiveSheet.Range("B162")

For Each myCell In myRng.Cells
Set DestCell = TopDest
myArr = Split(Application.Trim(myCell.Value), " ")
nCtr = 0
For iCtr = LBound(myArr) To UBound(myArr)
If IsNumeric(myArr(iCtr)) Then
DestCell.Value = myArr(iCtr)
Set DestCell = DestCell.Offset(0, 1)
nCtr = nCtr + 1
If nCtr = 4 Then
Exit For
End If
End If
Next iCtr
Set TopDest = TopDest.Offset(1, 0)
Next myCell
End Sub

--

Dave Peterson