| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I exact no. from text string? E.g ACBE123445te
From e.g I have ACBE122324eddf in cell A I want in cell B only 122324
|
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I exact no. from text string? E.g ACBE123445te
Hi Poonam
Assuming all your strings start with 4 letters and the number strings are always 6 digits long: =MID(A1,5,6) would work for a string in A1. Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Poonam" wrote in message ... From e.g I have ACBE122324eddf in cell A I want in cell B only 122324 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I exact no. from text string? E.g ACBE123445te
"Poonam" wrote:
From e.g I have ACBE122324eddf in cell A I want in cell B only 122324 One way .. 1. Install Rick Rothstein's ExtractNumber UDF below Press Alt+F11 to go to VBE Copy n paste Rick's UDF into the code window (everything within the dotted lines) Press Alt+Q to get back to Excel 2. In Excel, source data in A1 down Put in B1: =ExtractNumber(A1) Copy down '-------- Function ExtractNumber(rCell As Range) As Double Dim X As Long For X = 1 To Len(rCell.Value) If Mid$(rCell.Value, X, 1) Like "*[0-9.]" Then ExtractNumber = Val(Mid$(rCell.Value, X)) Exit For End If Next End Function '----- -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I exact no. from text string? E.g ACBE123445te
On Fri, 4 Apr 2008 14:30:01 -0700, Poonam
wrote: From e.g I have ACBE122324eddf in cell A I want in cell B only 122324 Assuming you only have one group of contiguous numbers: =LOOKUP(9.9E+307,--MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")),ROW(INDIRECT("1:"&LEN(A1))))) --ron |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I exact no. from text string? E.g ACBE123445te
Hi Ron,
Not sure if I'm allowed to hop into someone else's post. I tried your formula, but XL (2000) wouldn't accept it. Said there was an error. I don't know anything about array formulas, except that they have curly brackets. Please help. Dave. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I exact no. from text string? E.g ACBE123445te
Missed one step
It should read: Press Alt+F11 to go to VBE In VBE, click Insert Module Copy n paste Rick's UDF into the code window ... -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How can I exact no. from text string? E.g ACBE123445te
On Fri, 4 Apr 2008 18:55:00 -0700, Dave wrote:
Hi Ron, Not sure if I'm allowed to hop into someone else's post. I tried your formula, but XL (2000) wouldn't accept it. Said there was an error. I don't know anything about array formulas, except that they have curly brackets. Please help. Dave. It should work fine in Excel 2000. It contains some arrays, but does not need to be entered as an array formula. What was the error message? --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Change 3 letter text string to a number string | Excel Discussion (Misc queries) | |||
| vlookup and finding text string that's not an exact match | Excel Discussion (Misc queries) | |||
| TEST FOR EXACT TEXT | Excel Worksheet Functions | |||
| a exact string search inquiry | Excel Worksheet Functions | |||
| Splitting a text string into string and number | Excel Discussion (Misc queries) | |||
Linear Mode
