Thread: using mid find to either extract a number with 2 decimals or excludeFROM A STRING
View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld[_2_] Ron Rosenfeld[_2_] is offline
external usenet poster
  
Posts: 1,045
Default using mid find to either extract a number with 2 decimals or exclude FROM A STRING
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))
Reply With QuoteReply With Quote