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

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

"Fiona" wrote in message
...
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!!

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


"Fiona" wrote:

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

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" wrote:

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

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

"Sheeloo" wrote:

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


"Fiona" wrote:

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

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&"0123456 789")),6)

assuming the string is in A1

--


Regards,


Peo Sjoblom

"Fiona" wrote in message
...
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

"Sheeloo" wrote:

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


"Fiona" wrote:

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

Thank you, that's exactly what i needed !

"Peo Sjoblom" wrote:

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&"0123456 789")),6)

assuming the string is in A1

--


Regards,


Peo Sjoblom

"Fiona" wrote in message
...
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

"Sheeloo" wrote:

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


"Fiona" wrote:

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