extract number
 

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

extract number
 

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

extract number
 

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

extract number
 

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

extract number
 

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

extract number
 

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

extract number
 

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