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