using mid find to either extract a number with 2 decimals or excludeFROM A STRING

E

edwardsoares000

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
 
C

Claus Busch

Hi Ed,

Am Tue, 19 Feb 2013 09:57:40 -0800 (PST) schrieb
(e-mail address removed):
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:
Click to expand...

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
 
R

Ron Rosenfeld

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
Click to expand...

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

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

Ron Rosenfeld

Amount
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))
Click to expand...

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

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

Ron Rosenfeld

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

Amount
=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))
Click to expand...

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 :-|
 
E

edwardsoares000

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 :-|
Click to expand...

Thanks Ron and Claus.
 
Top