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

  • Thread starter Thread starter Poonam
  • Start date Start date
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
 
Poonam said:
From e.g I have ACBE122324eddf in cell A I want in cell B only 122324
Click to expand...

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


---
 
From e.g I have ACBE122324eddf in cell A I want in cell B only 122324
Click to expand...

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
 
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.
 
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.
Click to expand...

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