Extract Numbers from a text field whereever they are

If I have a text field and the data starts with numbers I know I can use VAL 
to extract the number but if the data starts with a letter VAL returns 0 -  
i.e A109 = 0 whilst 109A =109. How can I convert to a number wherever the 
text data actually is? I'm hoping there is a function for this as I don't 
really write code. Thanks for any help
0
Utf
3/6/2008 3:45:01 PM
access 16762 articles. 2 followers. Follow

3 Replies
1686 Views

Similar Articles

[PageSpeed] 40

On Thu, 6 Mar 2008 07:45:01 -0800, Sheila D wrote:

> If I have a text field and the data starts with numbers I know I can use VAL 
> to extract the number but if the data starts with a letter VAL returns 0 -  
> i.e A109 = 0 whilst 109A =109. How can I convert to a number wherever the 
> text data actually is? I'm hoping there is a function for this as I don't 
> really write code. Thanks for any help

Their is a function, but unfortunately it's User Defined, therefore
you have to write it yourself. I've done it for you here.

If the string is going to be like "A109", where the number value is
contiguous, then you can use:

Copy and paste the below code into a Module

Function FindNumbers(strIn As String) As Long

Dim intY As Integer
Dim intX As Integer

For intY = 1 To Len(strIn)

intX = Asc(Mid(strIn, intY))
If intX >= 49 And intX <= 58 Then
    FindNumbers = Val(Mid(strIn, intY))
    Exit For
End If
Next intY

End Function
_____________

You can call it using
=FindNumbers([FieldName])


However, if the string can be like "A1B0XVF9Z", where the number
values are intermixed with letter values and you wish to extract all
the numbers, then you have to do something like this:

Copy and paste the below code into a module.

Public Function FindAllNumbers(strIn as String) As Long

Dim intY As Integer
Dim intX As Integer

For intY = 1 To Len(strIn)

intX = Asc(Mid(strIn, intY))
If intX >= 48 And intX <= 58 Then
    FindAllNumbers = FindAllNumbers & Mid(strIn, intY, 1)
End If
Next intY

End Function
________________

You call this the same way:
=FindAllNumbers([FieldName])

Both functons will return 109 from their respective string.


-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
0
fredg
3/6/2008 4:14:05 PM
Fred you are a star, thank you

"fredg" wrote:

> On Thu, 6 Mar 2008 07:45:01 -0800, Sheila D wrote:
> 
> > If I have a text field and the data starts with numbers I know I can use VAL 
> > to extract the number but if the data starts with a letter VAL returns 0 -  
> > i.e A109 = 0 whilst 109A =109. How can I convert to a number wherever the 
> > text data actually is? I'm hoping there is a function for this as I don't 
> > really write code. Thanks for any help
> 
> Their is a function, but unfortunately it's User Defined, therefore
> you have to write it yourself. I've done it for you here.
> 
> If the string is going to be like "A109", where the number value is
> contiguous, then you can use:
> 
> Copy and paste the below code into a Module
> 
> Function FindNumbers(strIn As String) As Long
> 
> Dim intY As Integer
> Dim intX As Integer
> 
> For intY = 1 To Len(strIn)
> 
> intX = Asc(Mid(strIn, intY))
> If intX >= 49 And intX <= 58 Then
>     FindNumbers = Val(Mid(strIn, intY))
>     Exit For
> End If
> Next intY
> 
> End Function
> _____________
> 
> You can call it using
> =FindNumbers([FieldName])
> 
> 
> However, if the string can be like "A1B0XVF9Z", where the number
> values are intermixed with letter values and you wish to extract all
> the numbers, then you have to do something like this:
> 
> Copy and paste the below code into a module.
> 
> Public Function FindAllNumbers(strIn as String) As Long
> 
> Dim intY As Integer
> Dim intX As Integer
> 
> For intY = 1 To Len(strIn)
> 
> intX = Asc(Mid(strIn, intY))
> If intX >= 48 And intX <= 58 Then
>     FindAllNumbers = FindAllNumbers & Mid(strIn, intY, 1)
> End If
> Next intY
> 
> End Function
> ________________
> 
> You call this the same way:
> =FindAllNumbers([FieldName])
> 
> Both functons will return 109 from their respective string.
> 
> 
> -- 
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail
> 
0
Utf
3/6/2008 4:22:00 PM
You can create a table Alpha with fields ALPHA containing all the characters 
you may encounter.  Use it in a totals query unjoined provided you have a 
primary key.  Field [y] text field has numbers and number-letter combinations.

SELECT [Change Requests].Primary_Key, [Change Requests].[Date open], [Change 
Requests].[Date close], [Change Requests].y, Val(Replace([y],[ALPHA],"")) AS 
[Number Extract]
FROM ALPHA, [Change Requests]
GROUP BY [Change Requests].Primary_Key, [Change Requests].[Date open], 
[Change Requests].[Date close], [Change Requests].y, 
Val(Replace([y],[ALPHA],""));

-- 
KARL DEWEY
Build a little - Test a little


"Sheila D" wrote:

> If I have a text field and the data starts with numbers I know I can use VAL 
> to extract the number but if the data starts with a letter VAL returns 0 -  
> i.e A109 = 0 whilst 109A =109. How can I convert to a number wherever the 
> text data actually is? I'm hoping there is a function for this as I don't 
> really write code. Thanks for any help
0
Utf
3/6/2008 5:01:01 PM
Reply:

Similar Artilces:

how can I get the ticket numbers to increase correctly?
I'm working on raffle tickets but need more then 10 pages - how can I get the added pages to include the correct numbering? Take a look at this article: The Registry Fix for changing the Mail Merge Batch Size http://ed.mvps.org/Static.aspx?=Publisher/registryfix -- Mary Sauer http://msauer.mvps.org/ "tseifert" <tseifert@discussions.microsoft.com> wrote in message news:E6E74327-19C0-4F90-9146-279B9C741CAE@microsoft.com... > I'm working on raffle tickets but need more then 10 pages - how can I get the > added pages to include the correct numbering? ...

extracting icons
Is there any way to extract an icon from an application? Take a look at the ExtractIcon[Ex]() API Cheers Check Abdoul --------------------- <davegreb@gpxinc.com> wrote in message news:c6c54da6-574c-4c8e-8ec9-3b77b99a5523@d70g2000hsb.googlegroups.com... > Is there any way to extract an icon from an application? ...

extracting totals from within a spreadsheet
I need to know how to take different information from within a spreadsheet --where two conditions/catogories must apply so that a third column where i have inputed hours, will total for those conditions only... can anyone help? Please spell out what you want we are not mind readers -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jovetta" <Jovetta@discussions.microsoft.com> wrote in message news:91442C6D-03DF-4FC1-A069-AAB43FA57485@microsoft.com... >I need to know how to take different information from within a spreadsheet > --where two condi...

Matching call data based on date, time and number called to give c
I have the following two tables on different sheets, both containing other information within them that is irrelevant in my question. SHEET 1 Date - Time - Number Called Duration Cost 01/02/10 09:05:21 01234123456 02:16 0.50 SHEET 2 Date - Time Number Called - Duration Extension 01/02/10 09:07:56 01234123456 02:15 1234 01/02/10 15:30:45 01234123456 01:59 1234 Sheet 2 HAS A LOT MORE RECORDS THAN SHEET 1 AND THERE ARE A NUMBER OF CALLS TO THE SAME NUM...

Random numbers, Canadian Zip Code style #3
Cool, I'll give it a try. Thank you very much. Le -- Shocke ----------------------------------------------------------------------- Shocked's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1489 View this thread: http://www.excelforum.com/showthread.php?threadid=26523 ...

Plain text Outlook on Web
Does anybody know how to send email (preferable *all* email) as plain text, using the Outlook On Web browser interface? Many thanks in advance. -- Ed. Ed Weatherup <invalid@invalid.invalid> wrote: > Does anybody know how to send email (preferable *all* email) as plain > text, using the Outlook On Web browser interface? As far as I can tell, Outlook Web Access can't be made to send plain text format. -- Brian Tillman Brian Tillman wrote: > Ed Weatherup <invalid@invalid.invalid> wrote: > >> Does anybody know how to send email (preferable *all* ema...

Inclusive number of days and running total
Hi, I have a column of start dates (B) and a column of end dates (C). I would like to have the inclusive total of days. I would then like to have a running total of days for the callendar year as well as the total for any year and a grand total. Have been playing with DATEDIF but no luck so far. Cheers, Chris Just subtract the date in C from the date in B and format as General. Then SUM as required. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Chris" <Chris@discussions.microsoft.c...

Excel: extract and sum numerals from mixed text/numeral cell range
I have a large (30x20) grid of cells with data, and I want to extract and then sum up certain numerals from this entire range. The catch is that the data is mixed numerals and text, as you'll see below. Here's an abbreviated 3x3 example, with a value in each of the nine cells: V7.1 T H P1 A T B V3 P4.5 If I just wanted to sum up the instances of "T" appearing, I could use COUNTIF() for the entire range to come up with answer ("T" appears 2 times). Easy enough. But, what I'm trying to accomplish is to sum up the numerals associated w...

Filter to select from various fields on opening a form
I have a form called TDC and want to filter data in the following fields on opening the form: DiaryAction (text field); DiaryCategory (text field); DiarySubCategory (text field); Company (text field); ContactType (text field); DiaryItemClosed (a Yes/No field). At the same time, I want the option not to select anything in each of these fields. Please could somebody give me the steps to do this. I am doing this in Access 2007, but am creating it as an Access 2003 database. Thank you for any help. I meant to say that I want this from dropdown boxes, but forgot to say becau...

Extract emails from cells with text
I have a row in column A which includes an email address in the text that I'd like to extract to column B. Is there a formula I can use to accomplish extracting the email address only to column B? Here's an example of different cells in column A: Please email example@law.ufl.edu to contact us...... OR Schedule an appointment for assistance, or email example@uga.edu with your questions... Thank you Try this... All on one line: =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND (" ",A1&" ",FIND("@",A1))-1)," ", REPT(" ",...

Query and Extract
Hello all, This seems basic, but I just cannot get it. While using SQL Server 2005 and the Microsoft SQL Server Management Studio, I created a database called AssetQuote. Inside I have on table called assetquotes. From there, I have three columns, (date, quote, author) The column type for date is datetime and the other two are just text. My Query is something this: INSERT INTO AssetQuote (date, quote, author) VALUES('052010','No act of kindness, no matter how small, is ever wasted.', 'Aesop'); When I run the query I get this result: Msg 208, ...

extracting individual numbers from "ranges"
I'm trying to get EXCEL to extract stock prices quoted in individual cells as 52-week ranges --e.g. 11.76-19.90-- and do the following calculation: (higher price - lower price)/lower price Obviously, this a problem because each cell appears to handle the range as text or something. Is there are way to get EXCEL to handle the "text" (or however else it is interpreting what's in the cell) as individual numbers? Great thanks for any help. Hi do your entries always look like lower_number-higher_number if yes you may try the following formula in the adjacent row (lets assum...

Text Wrap
I have text wrap on a cell with lots of text and I use "Alt Enter" to create new lines within the cell. The last two entries in the cell are not wrapping though the first four entries within the cell wrap just fine. Is there a way to get the last two entries within the cell to text wrap? Thanks! Is the rowheight big enough? And if those last two entries are really long, then maybe a few more alt-enters to break them up???? Craig wrote: > > I have text wrap on a cell with lots of text and I > use "Alt Enter" to create new lines within the cell. The > l...

extracting hours from a sum of time
I have a cell which sum a bunch of cells containing an elapsed time. The sum totals to more than 24 hours. I have to a cell with a cost per hour and I need to caclculate the total cost. The problem is that the hour function returns values in the range of 0-24. My current sum is 25:30 and the hour function return 1 and not 25. Any ideas how to bypass it? It "returns" the correct number, but doesn't show. Format Custom as [h]:mm and it will. To convert to a number that you can multiply with an hourly rate, first multiply by 24; times in Excel are fractions of a day and a day is s...

Formatting Cells Containing Date Fields That Are Text
Someone here at work imported into Excel a column of text data that looks like mm/dd/yyyy form but need to be mmddyyyy. I thought I could simply highlight the columns, click on Format/Cells/Custom and create mmddyyyy. I do this but the data doesn't change UNTIL I highlight each cell and press F2 or double-click in each cell or create a formula using Right, Mid, etc. When I press F2 or double-click, I notice that an X and a check mark both appear to the right of the Row/ Column box. There's got to be an easier way to do this besides 'onesie-twosie' but we can't figure it...

Outline numbering in Excel?
Is there an 'easy' way to accomplish outline numbering similar to functionality in Word? I haven't found a built- in tool or feature, but I'm wondering if anyone has found or written a formula or macro which may accomplish this functionality? Any suggestions would be greatly appreciated. Patience is a virtue, Amanda. <g> AFAIK, there is no "easy" way to do outline numbering in XL as in Word. If you can explain in your own words what you are trying to do, you may get some suggestions. -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays &qu...

Extract Text
Dear all I have this text in a field: 200701-I 200701-II 200701-III 200701-IV and I need to separate only the data after the six first data: -I -II -III -IV How can I do it? Thanks a lot!!! Andr=E9. Take a look at Access HELP for the Mid() function. Open a query in design view and add a new field something like: NewField: Mid([YourField],7) -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ <gatarossi@ig.com.br> wrote in message news:11893397...

Can I extract unique cell values from every nth column?
I have a range that covers B5:XA160. For each row, data is entered in 11-cell groups: date, some numbers, description (a text value), more numbers, and more numbers. At first it was enough to merely count how many times certain descriptions appeared, because those were the only ones we would see - or so the story went. Now, I need to extract the unique descriptions AND provide a count! Actually, I'm cheating a bit. I'm using SUMPRODUCT to return the number of times the expected descriptions appear, and by subtracting these from the total number of text values I get a count of &quo...

Extraction Tool
Do anyone know of a tool or script which I can use to extract users email address from a windows 2000 domain we are using exchange 2000 w Here's a script to extract ALL email addresses form a domain: http://www.suneja.com/blog/2005/09/how-to-export-all-email-addresses-from.html Do you need something for a single user? -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "wmb" <wmb2003@uk2.net> wrote in message news:efxeuymMGHA.2276@TK2MSFTNGP15.phx.gbl... > Do anyone know of a tool or script which I ca...

Setting A Number Equal to Another Number
I was wondering if I could set a cell with one number equal to anothe number in another cell. I am having a hard time trying to explain wha I mean so I try to use an example. For example i have the number .93 in cell B3. I want the number i cell C3 to depend on the number in B3. For example if the number i between .90 and 1 I want the number in C3 to be 4, if the number i between .80 and .90 I want the number in C3 to be 3, and so on. Th number in cell B3 does change, so I want the number in C3 to chang based on the change of B3. Any help is greatly appreciated -- slag01 --------------...

Extracting data to make mailing list
My neighbour has a .PUBS name and address doc. The doc is 10 pages long. Each page of the document is a table with 10 rows and 3 columns. Each cell is name on top the line followed by 2 or 3 lines of address. She then just cuts the full sheet Avery into mailing labels. This has become too difficult to manage, sort, filter, etc. Anyone have a suggestion how to extract this data so I can make a Publisher mailing list? Thanks. -- Ross try bringing it into Excel...you'll some "massaging" to do. -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression "Ross"...

How to make Excel run limited number of formulas on a given worksh
Hello, I have a primary worksheet with defined named ranges. I reference these named ranges with about 10,000 SUMPRODUCT formulas on a secondary worksheet. The formulas all reference the same two defined columns, "Application", and "Date". The problem is that whenever I add rows of data to the primary worksheet, the secondary worksheet seems to be running all 10,000 formulas, and the performance is slow. Is there a way to write VBA scrip or something so that the secondary worksheet will only run the formulas applicable to the current date, like a Ge...

Recognizing misc. text
I have this worksheet in which a collumn contains a lot of text but i want to filter out the cells that contain the text "SAP (miscellaneous text)" from the rest. Is there any way excel can recognize the SAP+space part and doesn't care about what follows after that in the cell as long as there at least is some text? Thx in advance. -- MeisterHim ------------------------------------------------------------------------ MeisterHim's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27401 View this thread: http://www.excelforum.com/showthread.php?threadi...

consecutive numbering
I have created a form in Excel which I need to make 2000 copies of it, and they need to be consecutively numbered and then bound. Does anyone know how I set up a cell to consecutively print the next number on the following copy? hi ? Try this macro http://www.rondebruin.nl/print.htm#number -- Regards Ron de Bruin http://www.rondebruin.nl <msgevalia@hotmail.com> wrote in message news:1128975560.360859.292080@g47g2000cwa.googlegroups.com... >I have created a form in Excel which I need to make 2000 copies of it, > and they need to be consecutively numbered and then bound. Doe...

how to add text box
Is it possible to add a text box to a document, but without the black border around the text box? It is. Double-click the border of your textbox, then, under "Line", select the colour you'd like to use, or simply use "No line" to get rid of the lines altogether. On 29/11/06 9:45, in article 1164789931.183526.112550@h54g2000cwb.googlegroups.com, "lawengin" <skho@iinet.net.au> wrote: > Is it possible to add a text box to a document, but without the black > border around the text box? > -- Michel Bintener Microsoft MVP Office:Mac (Entourage...