extract number

[Fiona]

Hi
I wondered if anyone knew the formula to extract a block of numbers in
amongst a sentace, the numbers are a a different place in each line
(mr joe bloggs 12456
mrs smith hometown county 75864
mr tom jones county potcode 44456)

the numbers always contain 6 digits.

Thank you!!

 

[RagDyeR]

In your examples, the numbers are always at the end.

If that be the case, try:

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

If you want them to be true numbers, add the dbl unary:

=--TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Hi
I wondered if anyone knew the formula to extract a block of numbers in
amongst a sentace, the numbers are a a different place in each line
(mr joe bloggs 12456
mrs smith hometown county 75864
mr tom jones county potcode 44456)

the numbers always contain 6 digits.

Thank you!!

 

[Sheeloo]

are they always at the end as per your example?
If yes then use
=RIGHT(A1,6)

 

[JMay]

Here's a UDF (I use) - Place this into a Standard Module

Public Function ExtractNums(c) As String
Dim i As Integer
Dim MyNums As String

'Templatebuilder
'Returning numeric value from string'
MyNums = ""
For i = 1 To Len(c)
If InStr(1, "0123456789", Mid(c, i, 1), vbTextCompare) > 0 Then
MyNums = MyNums + Mid(c, i, 1)
End If
Next i
ExtractNums = MyNums
End Function

 

[Fiona]

Thank you for your message, sorry I didnt explain very well, the numbers are
always in the middle of the sentance with text either side, but the amount of
words either side varies on each line

 

[Peo Sjoblom]

If the numbers are always 6 digits you can use this formula


=MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&"0123456789")),6)

assuming the string is in A1

--


Regards,


Peo Sjoblom

 

[Fiona]

Thank you, that's exactly what i needed !

If the numbers are always 6 digits you can use this formula


=MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&"0123456789")),6)

assuming the string is in A1

--


Regards,


Peo Sjoblom