Extracting numbers from cell

[tricky81]

600120 Steve Hall
Stteph Day 600985
607776 Karen Jones
uk 600779 O'Neale

If I have a list similar to the above in a column. Is possible to extract just the 60*** numbers from the list and have them in the next column?

Many thanks in advance.

 

[Richard Schollar]

hi

Assuming those values in A1 downwards try:

=LOOKUP(100^100,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1)))))

It will extract the first string of numbers in the cell (not retsricted to those beginning 60) so may not do exactly what you want.

 

[HarryS]

From your example you are looking for 60**** type numbers


try something like this

ColFrom = 4 ' what it is
rowSt= 3 ' start row then looped

while cells(colfrom,rowst)<>""
p=instr(cells(colfrom,rowst),"60")
if p >0 then
cells(colfrom+1,rowst)=mid(cells(colfrom,rowst),p,6)
' adjust the 6 for size of numbers
end if
rowst=rowst+1
wend

 

[Richard Schollar]

Here's an updated one:

=IF(ISNUMBER(FIND(" 60"," "&A1)),MID(" "&A1,FIND(" 60"," "&A1),FIND(" ",A1&" ",FIND(" 60"," "&A1)+3)-FIND(" 60"," "&A1)+1),"")

restricts results to 60 numbers. Does presume there is always a space following the end of the 60 number (or the end of the string) and also always a space before the 60 number (or the start of the string).

 

[Jon von der Heyden]

=LOOKUP(100^100,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1)))))

The mind boggles with the stuff you come up with Rich!
You da man!