How can I exact no. from text string? E.g ACBE123445te

[Poonam]

From e.g I have ACBE122324eddf in cell A I want in cell B only 122324

 

[macropod]

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

 

[Max]

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


---

 

[Ron Rosenfeld]

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&"0123456789")),ROW(INDIRECT("1:"&LEN(A1)))))

--ron

 

[Dave]

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.

 

[Max]

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

---

 

[Ron Rosenfeld]

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