Here is my data:
DEPOSIT 440.00
DEPOSIT 99.07
WZ404 TFR-FR 7119109 3,982.00
CHQ~00022-2300103934 4,426.00
BC HYDRO Q3L2Z5 86.33
SVC PLAN 2 FEE 16.95

I want my data organised in 2 columns as follows:

Description Amount $

DEPOSIT 440.00
DEPOSIT 99.07
WZ404 TFR-FR 7119109 3,982.00
CHQ~00022-2300103934 4,426.00
BC HYDRO Q3L2Z5 86.33
SVC PLAN 2 FEE 16.95

Any help would be appreciated

Thanks, Ed

Hi Ed,

Am Tue, 19 Feb 2013 09:57:40 -0800 (PST) schrieb
:

Here is my data:
DEPOSIT 440.00
DEPOSIT 99.07
WZ404 TFR-FR 7119109 3,982.00
CHQ~00022-2300103934 4,426.00
BC HYDRO Q3L2Z5 86.33
SVC PLAN 2 FEE 16.95

I want my data organised in 2 columns as follows:

your data in column A:

Sub CutData()
Dim i As Integer
Dim rngC As Range
Dim LRow As Long

LRow = Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In Range("A1:A" & LRow)
i = InStrRev(rngC, " ")
rngC.Offset(0, 1) = Mid(rngC, i + 1, 10)
rngC = Mid(rngC, 1, i - 1)
Next
End Sub

or with formula in B1:
=LEFT(A1,FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ",))))-1)
and in C1:
=--(TRIM(SUBSTITUTE(A1,B1,)))



Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Tue, 19 Feb 2013 09:57:40 -0800 (PST), wrote:

Here is my data:
DEPOSIT 440.00
DEPOSIT 99.07
WZ404 TFR-FR 7119109 3,982.00
CHQ~00022-2300103934 4,426.00
BC HYDRO Q3L2Z5 86.33
SVC PLAN 2 FEE 16.95

I want my data organised in 2 columns as follows:

Description Amount $

DEPOSIT 440.00
DEPOSIT 99.07
WZ404 TFR-FR 7119109 3,982.00
CHQ~00022-2300103934 4,426.00
BC HYDRO Q3L2Z5 86.33
SVC PLAN 2 FEE 16.95

Any help would be appreciated

Thanks, Ed



Description
=LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),
LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-1)

Amount
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))

On Tue, 19 Feb 2013 14:02:36 -0500, Ron Rosenfeld wrote:

Amount
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))

And to make that a numeric value and not a text value, precede with a double unary

=--TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))

On Tue, 19 Feb 2013 14:02:36 -0500, Ron Rosenfeld wrote:

On Tue, 19 Feb 2013 09:57:40 -0800 (PST), wrote:

Here is my data:
DEPOSIT 440.00
DEPOSIT 99.07
WZ404 TFR-FR 7119109 3,982.00
CHQ~00022-2300103934 4,426.00
BC HYDRO Q3L2Z5 86.33
SVC PLAN 2 FEE 16.95

I want my data organised in 2 columns as follows:

Description Amount $

DEPOSIT 440.00
DEPOSIT 99.07
WZ404 TFR-FR 7119109 3,982.00
CHQ~00022-2300103934 4,426.00
BC HYDRO Q3L2Z5 86.33
SVC PLAN 2 FEE 16.95

Any help would be appreciated

Thanks, Ed



Description
=LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),
LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-1)

Amount
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))

And I just noticed that my approach for formula is the same as Claus. Had I seen the bottom of his post first, I would not have bothered posting :-|

On Tuesday, February 19, 2013 2:05:16 PM UTC-5, Ron Rosenfeld wrote:
On Tue, 19 Feb 2013 14:02:36 -0500, Ron Rosenfeld wrote:



On Tue, 19 Feb 2013 09:57:40 -0800 (PST), wrote:



Here is my data:

DEPOSIT 440.00

DEPOSIT 99.07

WZ404 TFR-FR 7119109 3,982.00

CHQ~00022-2300103934 4,426.00

BC HYDRO Q3L2Z5 86.33

SVC PLAN 2 FEE 16.95



I want my data organised in 2 columns as follows:



Description Amount $



DEPOSIT 440.00

DEPOSIT 99.07

WZ404 TFR-FR 7119109 3,982.00

CHQ~00022-2300103934 4,426.00

BC HYDRO Q3L2Z5 86.33

SVC PLAN 2 FEE 16.95



Any help would be appreciated



Thanks, Ed







Description

=LEFT(A2,FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),

LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-1)



Amount

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))



And I just noticed that my approach for formula is the same as Claus. Had I seen the bottom of his post first, I would not have bothered posting :-|

Thanks Ron and Claus.