| Home |
| Search |
| Today's Posts |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
using mid find to either extract a number with 2 decimals orexclude FROM A STRING
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. |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| EXTRACT NUMBER FROM STRING | Charts and Charting in Excel | |||
| Extract number from text/number string.. | Excel Discussion (Misc queries) | |||
| Extract Only Number From A String | Excel Programming | |||
| How to extract the Number from a String | New Users to Excel | |||
| Extract 2, 3, 4 or 5-digit number from string | Excel Programming | |||
Threaded Mode
