Truncate / Substitute Formulas

Is there a formula that removes everything except numbers from a cell?  For 
example, if a cell contained the text FW345A-A the formula would return the 
value 345.  I got it to somewhat work using SUBSTITUTE on top of itself, but 
there seems to be a limit in the number of characters you can add to the 
string.  It won't let you add all 26 letters and the dash.
0
Utf
3/3/2010 9:07:05 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
469 Views

Similar Articles

[PageSpeed] 27

>FW345A-A

Are the numbers to extract *ALWAYS* together?

Will there ever be strings like this:

F5W345A-1
1-FW345A-A
FW34A5A-A

-- 
Biff
Microsoft Excel MVP


"Jason W" <Jason W@discussions.microsoft.com> wrote in message 
news:59A13DED-7DBC-405A-8B8D-76429A153D33@microsoft.com...
> Is there a formula that removes everything except numbers from a cell? 
> For
> example, if a cell contained the text FW345A-A the formula would return 
> the
> value 345.  I got it to somewhat work using SUBSTITUTE on top of itself, 
> but
> there seems to be a limit in the number of characters you can add to the
> string.  It won't let you add all 26 letters and the dash. 


0
T
3/3/2010 9:55:50 PM
See this page:
http://office.microsoft.com/en-au/excel/HA011549011033.aspx
At the end of the article you'll have this formula (which would be in a cell 
like B1 or C1 and refers to a value such as FW345A-A in cell A1):
=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))

Note that it is an array formula, so you must 'commit' it with 
[Shift]+[Ctrl]+[Enter] so that it ends up looking like
{=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))}
in the formula bar.  You can now fill that formula down the sheet as far as 
you need to take it.

So, thanks to Ashish Mathur, there is a solution.

"Jason W" wrote:

> Is there a formula that removes everything except numbers from a cell?  For 
> example, if a cell contained the text FW345A-A the formula would return the 
> value 345.  I got it to somewhat work using SUBSTITUTE on top of itself, but 
> there seems to be a limit in the number of characters you can add to the 
> string.  It won't let you add all 26 letters and the dash.
0
Utf
3/3/2010 10:30:08 PM
You need to tell us more about your values. For example... Are the numbers 
always 3 digits long? Does the number always start at position 3 in the 
text? Will there ever be other numbers in the text (such as FW345A-A67B) 
and, if so, what do you want to do with them? Anything else you can think of 
that will describe what you have and what you want to get from it?

-- 
Rick (MVP - Excel)


"Jason W" <Jason W@discussions.microsoft.com> wrote in message 
news:59A13DED-7DBC-405A-8B8D-76429A153D33@microsoft.com...
> Is there a formula that removes everything except numbers from a cell? 
> For
> example, if a cell contained the text FW345A-A the formula would return 
> the
> value 345.  I got it to somewhat work using SUBSTITUTE on top of itself, 
> but
> there seems to be a limit in the number of characters you can add to the
> string.  It won't let you add all 26 letters and the dash. 

0
Rick
3/3/2010 10:31:08 PM
Reply:

Similar Artilces:

Help with Array Formula #2
Hi there, I have a spreadsheet with 7 columns (named Bird, Cow, Horse, Rabbit, Pig, Cat, Dog) and 23 rows that have either yes or no in each cell - mainly no's. If there is a 'yes' in Bird then there should be 'no' in each of the other 6 columns (Cow, Horse, Rabbit, Pig, Cat, Dog). Basically, if there is a 'yes' in Bird then they have identified themselves as 'Bird' rather than any other animal. I then have 10 more columns (named ans1, ans2, ans3, etc) that each have responses such as "A", "B", "C", "D", &quo...

Struggling with formula
Hi I have a spreadsheet withthe following data: 28/08/2008 09/05/2008 HINGE SA28553 8 =A31.10 =A30.77 =A36.16 28/08/2008 09/05/2008 HINGE SA28566 8 =A31.10 =A30.77 =A36.16 20/08/2008 28/08/2008 HINGE SA28389 24 =A31.10 =A30.77 =A318.48 14/08/2008 21/08/2008 HINGE SA28268 12 =A30.00 =A30.00 What I need to see is the total number of units, of which XXX are Free of Charge between xxxx date Sorry, but I am struggling and got so far =3DCOUNTIF(J2:J924,"=A30.00") but struggling with the rest, can anyone help Ta BabyPink, In general: =SUMPRODUCT((FirstDate<=Date"))...

Trouble with copy and past of formulas in Excel
I got a sheet from one of my collegaes that was causing problems, it sometimes looses formulas (It looks like a problem with connections through our network) and you get true untrue name errors, after refreshing the cell the problem is gone. Now I'm having trouble with copying formulas, after trying some copy and paste it crashes with dr. Watson. It seems that there is some timeout problem. More people have trouble with this with the somewhat larger sheets. ...

Formulas inside Pivot Tqables
I just upgraded to Excel 2003 and I can no longer do formulas in pivot tables (in Excel 2000, Right-click inside a pivot table, the 'formulas', then 'calculated field' or 'calculated item'). There is no "formulas' opein in Excel 2003. Any ideas? ...

Changing cell from formula to data.
Is there a way to change a cell from a formula (that is returning data) to the data it is returning. I realy hop it it becuse it would help out a ton. -- Thanks TimM If you select the cell and copy then edit - pastes special - values on the cell you will have the value and not the formula. HTH -- Sincerely, Michael Colvin "TimM" wrote: > Is there a way to change a cell from a formula (that is returning data) to > the data it is returning. I realy hop it it becuse it would help out a ton. > > > -- > Thanks > TimM Hi, Select the cell(s) contain...

Excel formulas 01-06-10
I have a worksheet that we would like to use to calculate price + labor for work. The price formula has me stumped. I would like to be able to put in the price in one box and have the formula determine the amount at which to multiply it by. For example: Price = 20.00 then the formula would determine how many to multiply it by (3.25) from the range given below. Range is 0-2.00 *4 2.01-5 *3.75 5.01-10 *3.5 10.01-20.00 *3.25 20.01-40 *3 40.01-60 *2.75 60.01-100 *2.5 100.01-150 *2.25 >150.01 *2 I have the labor formula and then the total is a sum formula. Please help...

Countif formula
Hi, I'm new here and I hope I'm posting in the right forum. I require help with a formula that has left me scratching my head for a couple of hours. I have a column of data cells that lists 3 different options, for example: option1 = Good option2 = Ok option3 = Bad i need a formula that will count how many 'Good' entries there are and to work it out as a percentage of the total number of entries (all 3 options). I hope that makes sense. If more info is required, please let me know. Thanks for your time -- godonlyknows --------------------------------------------------...

Help with Lookup/Sum Formula
I have a spreedsheet with this type of data, for example Product Group Quantity Size 2812 10 0.75 2812 100 0.75 2812 300 0.5 2817 100 0.25 2817 200 1 2845 1000 1.5 I want to be able to set up a formula that you enter a product grou (2812) in a cell and it will for that product group go and sum up al the quantities for each size. The formula would go in the Sum colum For Example ...

Need a formula that tags one table based 2 columns in each table
I have two tables on one worksheet, Table A and Table B. Each table contains two column with X and Y coordinates, all values are numerical. The coordinate system is irrelevant. I need to "tag" all XY coordinates in Table B that have a matching XY coordinate in Table A. If Table A coordinates are in columns A and B, and Table B coordinates are in columns C and D, then I want to place the text "hit" in column E next to each coordinate pair from columns C and D that match a coordinate pair in columns A and B. The ranges for the respective tables have been named ACOORD and...

Formulas to highlight cell if condition is met
i wonder if there is any way i can highlight a cell if a condition is met? eg i want to highlight the cell if the num in the cell is greater than 1. do i hv tio use macros? -- hmmm ------------------------------------------------------------------------ hmmm's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25836 View this thread: http://www.excelforum.com/showthread.php?threadid=481784 No you can use conditional formatting Select the cell Menu Format>Conditional Formatting Set to greater than Set value to 1 Click the Format button Select the Pattern Tab ...

Formula to display Count of Days in given month, using list of Start and End Dates
I have a large number of rows that contain a start date and end date. I am attempting to generate a count of days within that date range that are in a particular month, for example, Jan of 2011. Any suggestions for a formula that could be used to display the amounts shown in Column C would be helpful. Layout is: A B C Start Date End Date Count of Days in Jan2011 1/10/11 4/7/11 21 2/6/11 3/1/11 0 12/20/10 2/15/11 31 Etc. Dave, I think the best way is to create a table f...

Help With Formula #5
I have data in a worksheet A B C 1 Verify 2% 99% 2 Audit 2% 98% 3 Account 5% 97% 4 Forward 4% 96% 5 Place 5% 95% I have another cell (F1) in the sheet that i would like to list the text in A1, but only if B1 is at least 5% if not then I want it to list A2, but only if B2 is at least 5% and so on down to A5. Please Help. What happens with row 3 and row 5? -- Don Guillett SalesAid Software donaldb@281.com "Report Lackey" <anonymous@discussions.microsoft.com> wrote in message news:033b01c3d62d$ebd800e0$a401280a@phx....

Excel formula needed or how to
I have got a rota with E for Early, L for Late and N for Night. For one particular date, e.g. in C3 there is the date, and C4 to C8 ar the various E , L and N. Now on the other side I want see who is actually working E, L or N at quick glance and requires a formula to convert the E to the nam allocated to that person and can it be done in such a way to omit th person who is not doing the E from the column of names and avoid an blank rows. Attached is a brief example but there is about 35 staff on that. 4/7/04 5/7/04 6/7/04 John EL EL EL Paul L O L Charlie N N O Jennife O L N Charlene EL ...

How do I set up a weighted formula in Excel?
Can someone help me please? I need to do a weighted formula in Excel 2007, where the total of the numbers equals 90% of one number and 10% of another number. Is it possible to do this? On Mon, 10 May 2010 15:53:01 -0700, ib1qb <ib1qb@discussions.microsoft.com> wrote: >Can someone help me please? I need to do a weighted formula in Excel 2007, >where the total of the numbers equals 90% of one number and 10% of another >number. Is it possible to do this? > =sumproduct(numbers, weights) e.g. =sumproduct(a1:a2,{0.9,0.1}) --ron Sa g1 to j7 conta...

Excel formula calculating between #'s
I am working on a bonus chart and this is what I am trying to do: If an employee keeps his register balances between -$10.00 thr +$10.00, they recieve 100 points. If they are out of that range the get 0 points. I have tried playing with "IF" formulas until I am blu in the face. Please help Thanks Mike Manic -- Message posted from http://www.ExcelForum.com Hi Mike try something like =IF(AND(A1>=-10,A1<=10),100,0) -- Regards Frank Kabel Frankfurt, Germany "manickmj >" <<manickmj.15ftz3@excelforum-nospam.com> schrieb im Newsbeitrag news:manickmj.1...

Save a date as a variable and run/convert formula against the vari
Good Day, What I'm needing to do is retrieve a date field from a closed workbook, save it as a variable and then calculate a week number based on it. which I'll use for another function. I've attached two types of code I'm struggling with in VB and hope someone can help... The first part retrieves a date from a close workbook and I need to change "MsgBox" to a varible to save the date retrieved. Sub GetVal() MsgBox TheValue("C:\GssReports", "gssreport MTTR.xlsx", "gssreport 1 ", "K6") End Sub Function...

help with formulas
I have a list of applications that have gone overdue. Here is a sample of my list: Program Code Case # Delay Code P60 401133012 P60 721283900 AG P30 821362112 P30 828190110 P60 950351513 CI L60 217075010 CI S60 810474413 OA Q30 235391903 OA S60 167004803 OA S30 893474708 OA S30 676254115 CI S60 712233808 There are several different program codes for the applications, and a few different delay codes, leading to several differe...

Translate excel formulae
Does anyone know if there exists a 'document' showing translation of excel formulae from English to French (e.g. =counta is =NBVAL)? -- Helen (English, but living in France) KeepItCool has a translator utility at: http://members.chello.nl/keepitcool/download.html Helen wrote: > > Does anyone know if there exists a 'document' showing translation of excel > formulae from English to French (e.g. =counta is =NBVAL)? > -- > Helen > (English, but living in France) -- Dave Peterson Thanks Dave, although I can't open their download without deactivating th...

Word wrap macro deletes formula
I created a macro to wrap text on a protected worksheet in which each cell contains a formula importing text from another worksheet. The cells are locked and formulas hidden but when I run the macro, it wraps the text and deletes the formula> how can I stop it doing this? The macro is as follows: Sub Wordwrap() ' ' Wordwrap Macro ' Macro recorded 01/10/2004 ' ' Keyboard Shortcut: Ctrl+w ' With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False ...

Help with creating a formula
Hello! I am trying to create a formula that will sum up certain criteria from one column as it relates to criteria in the same row in another column. For example, I want to sum up all the entries that begin with the letter "H" in column A as it relates to an entry of "2" from column B that is in the same row as the "H" entry from column A. COLUMN A COLUMN B H10 2 G01 2 H09 3 H15 2 H12 2 If there is such a formula...

Excel does not calculate my formulas
This is very strange...Excel does not calculate any formula. I can do a simple =A1+A2, and it just return a zero value. I made sure of the following: - Cell is set to General (tried it with different cell settings too) - Autocalc is on in settings (even a manual F9 calc doesn't work) -- Martin Verville We might need a few more clues: What is in A1? What is in A2? -- David Biddulph "Martin" <Martin@discussions.microsoft.com> wrote in message news:A9A25385-5BE8-4D38-A7A7-15E523C69DD9@microsoft.com... > This is very strange...Excel does not calculat...

FORMULAS #35
WHERE CAN I GET A LIST OF ALL FORMULAS MICROSOFT EXCEL USES? Excel's help for worksheet functions? Take a look at Peter Nonely's workbook that describes lots of functions: http://homepage.ntlworld.com/noneley/ If Peter's site isn't working, but Ron deBruin has a copy at: http://www.rondebruin.nl/files/xlfdic01.zip Norman Harker has his version at Debra Dalgleish's site: http://www.contextures.com/functions.html BLUPHISH wrote: > > WHERE CAN I GET A LIST OF ALL FORMULAS MICROSOFT EXCEL USES? -- Dave Peterson ...

Formula #16
I need to have a formula that can add a row of cells with a variable number of colmns. Columns will be added and deleted everyday, so the formula needs to have a variable range. I hope this is clear. Thanks, Bernie ...

Excel formula copied down
Is there a way to copy down a column this formula =F3*A2 and have the A2 stay the same all the way down? The F3 would change on each row, F4, F5 etc. but the A2 would stay 'constant'. Thanks in advance for any help! =F3*A$2 -- Ian -- "Pat" <Pat@discussions.microsoft.com> wrote in message news:73505B29-538A-48C7-8F2F-F6126EF2126B@microsoft.com... > Is there a way to copy down a column this formula =F3*A2 and have the A2 > stay > the same all the way down? The F3 would change on each row, F4, F5 etc. > but > the A2 would stay 'constant'...

polynomial formula for a line graph...
Hi. I haven't looked at this feature in a couple of years. Excel can give you the formula for a charted function... what I mean is, given a few data points, it can create a best fit curve, and display the function that it used to do that... Would someone remind me where, please? Thanks. Hi, Right click the data series and pick Add Trendline. From the dialog you can choose the type of trend line and whether to display the formula. Cheers Andy mark wrote: > Hi. > > I haven't looked at this feature in a couple of years. > > Excel can give you the formula for ...