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

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

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
---

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

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.

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
---

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