Hi
I have asked this before, but still need help.
How can I break up this in three columns-Date, Item, and Amount?
Thanks


03/06 WALGREENS #6061 UPLAND CA -11.30
03/09 AUTOMATIC PAYMENT - THANK YOU -3,174.23
02/11 SPROUTS FARMERS MAR CLAREMONT CA 17.65
02/12 WAL-MART UPLAND CA 16.43
02/13 EXXONMOBIL 79298477 CLAREMONT CA 10.00
02/13 WAL-MART UPLAND CA 6.47

What responses did you get before? The 'tough' part is splitting off the
Amount (which is identified as being that which follows the last space in the
entry).


"da" wrote:

Hi
I have asked this before, but still need help.
How can I break up this in three columns-Date, Item, and Amount?
Thanks


03/06 WALGREENS #6061 UPLAND CA -11.30
03/09 AUTOMATIC PAYMENT - THANK YOU -3,174.23
02/11 SPROUTS FARMERS MAR CLAREMONT CA 17.65
02/12 WAL-MART UPLAND CA 16.43
02/13 EXXONMOBIL 79298477 CLAREMONT CA 10.00
02/13 WAL-MART UPLAND CA 6.47

Thanks
Unfortunately, I don't remember what was the earlier response. But it did
break up the column in three different columns, with each separate heading.

"JLatham" wrote:

What responses did you get before? The 'tough' part is splitting off the
Amount (which is identified as being that which follows the last space in the
entry).


"da" wrote:

Hi
I have asked this before, but still need help.
How can I break up this in three columns-Date, Item, and Amount?
Thanks


03/06 WALGREENS #6061 UPLAND CA -11.30
03/09 AUTOMATIC PAYMENT - THANK YOU -3,174.23
02/11 SPROUTS FARMERS MAR CLAREMONT CA 17.65
02/12 WAL-MART UPLAND CA 16.43
02/13 EXXONMOBIL 79298477 CLAREMONT CA 10.00
02/13 WAL-MART UPLAND CA 6.47

Having already asked the question about what assistance you got before, and
still thinking that it may be easy to take that help and give you more to go
with it and get you going, I'll tell you how I would solve the problem:

I would use two User Defined Functions (UDF) which are nothing more than VB
code (macros) of a specific type that can be used in a worksheet cell just as
can any built-in Excel worksheet function. More about them, and their code
later.

But with those having been built, then you can do the following, assuming
that your main entries are in column A and begin on row 1, so the 03/06
WALGREENS #6061 UPLAND CA -11.30 entry is in Cell A1.

In B1 use some regular Excel worksheet functions to create this formula:
=LEFT(A1,FIND(" ",A1)-1)
that will peel off the 03/06 portion and put it into B1.

In C1 I'd put this formula which refers to one of the UDFs we will create in
a minute (best not to do this until the UDFs are in your workbook).

=itemdescription(A1)
That is going to pull out whatever is between the 1st and last space
characters in A1 and put it into C1 as the description.

Similarly, in D1, put in this formula which will refer to the 2nd UDF we
will create:
=ItemValue(A1)
that may appear as =itemvalue(A1) and that's just fine.

Now here is the code for the two UDFs and I'll tell you how to get it into
your workbook after it is listed:


Function ItemDescription(anycell As Range) As String
Dim startPoint As Integer
Dim endPoint As Integer
'find first space character in source cell
startPoint = InStr(anycell, " ") + 1
'find last space character in source cell
endPoint = InStrRev(anycell, " ")
'pull out stuff between 1st and last spaces
ItemDescription = Mid(anycell, startPoint, _
endPoint - startPoint)
End Function
Function ItemValue(anycell) As Currency
Const numericChars = "0123456789.-+"
Dim tempValue As String
Dim numCharsOnly As String
Dim LC As Integer
'save the section that follows the last
'space character in source cell
tempValue = Right(anycell, Len(anycell) - _
InStrRev(anycell, " "))
'save characters that are considered to
'be numeric (throws away commas, $ signs, etc)
For LC = 1 To Len(tempValue)
If InStr(numericChars, Mid(tempValue, LC, 1)) 0 Then
numCharsOnly = numCharsOnly & Mid(tempValue, LC, 1)
End If
Next
'convert results into a real number
'so we can do math with it properly
ItemValue = Val(numCharsOnly)
End Function


To get the code into your workbook: Open the workbook and press [Alt]+[F11]
to open the VB Editor (VBE). In the VBE, choose Insert | Module from its
menu.

Copy the code above and paste it into the module that was presented to you
in the previous step. Save your workbook. Now you should be able to safely
enter the two formulas as I showed earlier, and they should give you results
as long as macros are enabled in the workbook.
"da" wrote:

Hi
I have asked this before, but still need help.
How can I break up this in three columns-Date, Item, and Amount?
Thanks


03/06 WALGREENS #6061 UPLAND CA -11.30
03/09 AUTOMATIC PAYMENT - THANK YOU -3,174.23
02/11 SPROUTS FARMERS MAR CLAREMONT CA 17.65
02/12 WAL-MART UPLAND CA 16.43
02/13 EXXONMOBIL 79298477 CLAREMONT CA 10.00
02/13 WAL-MART UPLAND CA 6.47

Thank you. This will require more time to understand and apply. I am not that
expert with Excel. However, I will try.

"JLatham" wrote:

Having already asked the question about what assistance you got before, and
still thinking that it may be easy to take that help and give you more to go
with it and get you going, I'll tell you how I would solve the problem:

I would use two User Defined Functions (UDF) which are nothing more than VB
code (macros) of a specific type that can be used in a worksheet cell just as
can any built-in Excel worksheet function. More about them, and their code
later.

But with those having been built, then you can do the following, assuming
that your main entries are in column A and begin on row 1, so the 03/06
WALGREENS #6061 UPLAND CA -11.30 entry is in Cell A1.

In B1 use some regular Excel worksheet functions to create this formula:
=LEFT(A1,FIND(" ",A1)-1)
that will peel off the 03/06 portion and put it into B1.

In C1 I'd put this formula which refers to one of the UDFs we will create in
a minute (best not to do this until the UDFs are in your workbook).

=itemdescription(A1)
That is going to pull out whatever is between the 1st and last space
characters in A1 and put it into C1 as the description.

Similarly, in D1, put in this formula which will refer to the 2nd UDF we
will create:
=ItemValue(A1)
that may appear as =itemvalue(A1) and that's just fine.

Now here is the code for the two UDFs and I'll tell you how to get it into
your workbook after it is listed:


Function ItemDescription(anycell As Range) As String
Dim startPoint As Integer
Dim endPoint As Integer
'find first space character in source cell
startPoint = InStr(anycell, " ") + 1
'find last space character in source cell
endPoint = InStrRev(anycell, " ")
'pull out stuff between 1st and last spaces
ItemDescription = Mid(anycell, startPoint, _
endPoint - startPoint)
End Function
Function ItemValue(anycell) As Currency
Const numericChars = "0123456789.-+"
Dim tempValue As String
Dim numCharsOnly As String
Dim LC As Integer
'save the section that follows the last
'space character in source cell
tempValue = Right(anycell, Len(anycell) - _
InStrRev(anycell, " "))
'save characters that are considered to
'be numeric (throws away commas, $ signs, etc)
For LC = 1 To Len(tempValue)
If InStr(numericChars, Mid(tempValue, LC, 1)) 0 Then
numCharsOnly = numCharsOnly & Mid(tempValue, LC, 1)
End If
Next
'convert results into a real number
'so we can do math with it properly
ItemValue = Val(numCharsOnly)
End Function


To get the code into your workbook: Open the workbook and press [Alt]+[F11]
to open the VB Editor (VBE). In the VBE, choose Insert | Module from its
menu.

Copy the code above and paste it into the module that was presented to you
in the previous step. Save your workbook. Now you should be able to safely
enter the two formulas as I showed earlier, and they should give you results
as long as macros are enabled in the workbook.
"da" wrote:

Hi
I have asked this before, but still need help.
How can I break up this in three columns-Date, Item, and Amount?
Thanks


03/06 WALGREENS #6061 UPLAND CA -11.30
03/09 AUTOMATIC PAYMENT - THANK YOU -3,174.23
02/11 SPROUTS FARMERS MAR CLAREMONT CA 17.65
02/12 WAL-MART UPLAND CA 16.43
02/13 EXXONMOBIL 79298477 CLAREMONT CA 10.00
02/13 WAL-MART UPLAND CA 6.47

Hi
I found the earlier response.
Select all data
Choose Data
Text to Columns
Select Fixed With
Click Finish


"JLatham" wrote:

Having already asked the question about what assistance you got before, and
still thinking that it may be easy to take that help and give you more to go
with it and get you going, I'll tell you how I would solve the problem:

I would use two User Defined Functions (UDF) which are nothing more than VB
code (macros) of a specific type that can be used in a worksheet cell just as
can any built-in Excel worksheet function. More about them, and their code
later.

But with those having been built, then you can do the following, assuming
that your main entries are in column A and begin on row 1, so the 03/06
WALGREENS #6061 UPLAND CA -11.30 entry is in Cell A1.

In B1 use some regular Excel worksheet functions to create this formula:
=LEFT(A1,FIND(" ",A1)-1)
that will peel off the 03/06 portion and put it into B1.

In C1 I'd put this formula which refers to one of the UDFs we will create in
a minute (best not to do this until the UDFs are in your workbook).

=itemdescription(A1)
That is going to pull out whatever is between the 1st and last space
characters in A1 and put it into C1 as the description.

Similarly, in D1, put in this formula which will refer to the 2nd UDF we
will create:
=ItemValue(A1)
that may appear as =itemvalue(A1) and that's just fine.

Now here is the code for the two UDFs and I'll tell you how to get it into
your workbook after it is listed:


Function ItemDescription(anycell As Range) As String
Dim startPoint As Integer
Dim endPoint As Integer
'find first space character in source cell
startPoint = InStr(anycell, " ") + 1
'find last space character in source cell
endPoint = InStrRev(anycell, " ")
'pull out stuff between 1st and last spaces
ItemDescription = Mid(anycell, startPoint, _
endPoint - startPoint)
End Function
Function ItemValue(anycell) As Currency
Const numericChars = "0123456789.-+"
Dim tempValue As String
Dim numCharsOnly As String
Dim LC As Integer
'save the section that follows the last
'space character in source cell
tempValue = Right(anycell, Len(anycell) - _
InStrRev(anycell, " "))
'save characters that are considered to
'be numeric (throws away commas, $ signs, etc)
For LC = 1 To Len(tempValue)
If InStr(numericChars, Mid(tempValue, LC, 1)) 0 Then
numCharsOnly = numCharsOnly & Mid(tempValue, LC, 1)
End If
Next
'convert results into a real number
'so we can do math with it properly
ItemValue = Val(numCharsOnly)
End Function


To get the code into your workbook: Open the workbook and press [Alt]+[F11]
to open the VB Editor (VBE). In the VBE, choose Insert | Module from its
menu.

Copy the code above and paste it into the module that was presented to you
in the previous step. Save your workbook. Now you should be able to safely
enter the two formulas as I showed earlier, and they should give you results
as long as macros are enabled in the workbook.
"da" wrote:

Hi
I have asked this before, but still need help.
How can I break up this in three columns-Date, Item, and Amount?
Thanks


03/06 WALGREENS #6061 UPLAND CA -11.30
03/09 AUTOMATIC PAYMENT - THANK YOU -3,174.23
02/11 SPROUTS FARMERS MAR CLAREMONT CA 17.65
02/12 WAL-MART UPLAND CA 16.43
02/13 EXXONMOBIL 79298477 CLAREMONT CA 10.00
02/13 WAL-MART UPLAND CA 6.47

Thanks for getting back with that prior response. I'm not certain it's going
to work for you since the 'description' area appears to be of variable length.

If you have any questions or need more help with what I provided, either
reply to this or one of my other postings in this thread or you're welcome to
contact me more directly at (remove spaces)
Help From @ jlathamsite. com



"da" wrote:

Hi
I found the earlier response.
Select all data
Choose Data
Text to Columns
Select Fixed With
Click Finish


"JLatham" wrote:

Having already asked the question about what assistance you got before, and
still thinking that it may be easy to take that help and give you more to go
with it and get you going, I'll tell you how I would solve the problem:

I would use two User Defined Functions (UDF) which are nothing more than VB
code (macros) of a specific type that can be used in a worksheet cell just as
can any built-in Excel worksheet function. More about them, and their code
later.

But with those having been built, then you can do the following, assuming
that your main entries are in column A and begin on row 1, so the 03/06
WALGREENS #6061 UPLAND CA -11.30 entry is in Cell A1.

In B1 use some regular Excel worksheet functions to create this formula:
=LEFT(A1,FIND(" ",A1)-1)
that will peel off the 03/06 portion and put it into B1.

In C1 I'd put this formula which refers to one of the UDFs we will create in
a minute (best not to do this until the UDFs are in your workbook).

=itemdescription(A1)
That is going to pull out whatever is between the 1st and last space
characters in A1 and put it into C1 as the description.

Similarly, in D1, put in this formula which will refer to the 2nd UDF we
will create:
=ItemValue(A1)
that may appear as =itemvalue(A1) and that's just fine.

Now here is the code for the two UDFs and I'll tell you how to get it into
your workbook after it is listed:


Function ItemDescription(anycell As Range) As String
Dim startPoint As Integer
Dim endPoint As Integer
'find first space character in source cell
startPoint = InStr(anycell, " ") + 1
'find last space character in source cell
endPoint = InStrRev(anycell, " ")
'pull out stuff between 1st and last spaces
ItemDescription = Mid(anycell, startPoint, _
endPoint - startPoint)
End Function
Function ItemValue(anycell) As Currency
Const numericChars = "0123456789.-+"
Dim tempValue As String
Dim numCharsOnly As String
Dim LC As Integer
'save the section that follows the last
'space character in source cell
tempValue = Right(anycell, Len(anycell) - _
InStrRev(anycell, " "))
'save characters that are considered to
'be numeric (throws away commas, $ signs, etc)
For LC = 1 To Len(tempValue)
If InStr(numericChars, Mid(tempValue, LC, 1)) 0 Then
numCharsOnly = numCharsOnly & Mid(tempValue, LC, 1)
End If
Next
'convert results into a real number
'so we can do math with it properly
ItemValue = Val(numCharsOnly)
End Function


To get the code into your workbook: Open the workbook and press [Alt]+[F11]
to open the VB Editor (VBE). In the VBE, choose Insert | Module from its
menu.

Copy the code above and paste it into the module that was presented to you
in the previous step. Save your workbook. Now you should be able to safely
enter the two formulas as I showed earlier, and they should give you results
as long as macros are enabled in the workbook.
"da" wrote:

Hi
I have asked this before, but still need help.
How can I break up this in three columns-Date, Item, and Amount?
Thanks


03/06 WALGREENS #6061 UPLAND CA -11.30
03/09 AUTOMATIC PAYMENT - THANK YOU -3,174.23
02/11 SPROUTS FARMERS MAR CLAREMONT CA 17.65
02/12 WAL-MART UPLAND CA 16.43
02/13 EXXONMOBIL 79298477 CLAREMONT CA 10.00
02/13 WAL-MART UPLAND CA 6.47