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

  • Thread starter Thread starter edwardsoares000
  • Start date Start date
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
 
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
 
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.
 
Back
Top