deleting numbers shorter than x characters

[Christiano]

Hello, I have the following request, if someone is kind to help me.
The lines in excel look like this :

3 4 6353 18423875632
43 563 88888888
123 4444 55555555

Now, I want to erase all the numbers from these 3 rows EXCEPT the numbers longer than 5 characters. In the end, I want the sheet to look like this :

18423875632
88888888
55555555

Is this possible ?

Thank you very much,

Cristian

 

[Andrew Poulsom]

Welcome to MrExcel.

Are the long numbers always at the end?

 

[Jon von der Heyden]

Welcome to the forum.
Perhaps;
=MAX(--MID(SUBSTITUTE(" "&A1," ",REPT(" ",255)),255*ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1)),255))

 

[Jon von der Heyden]

Welcome to MrExcel.

Are the long numbers always at the end?

Good point! If so then I would adapt my original to:
=LOOKUP(9.99E+307,--MID(SUBSTITUTE(" "&A1," ",REPT(" ",255)),255*{1;2;3;4;5;6;7;8;9},255))

 

[Christiano]

Hello!
Glad to be here!

Thank you for the quick reply.
Sadly, no one works well. Or I don't know how to set it properly.
Where in the formula is the "x" number of characters ?

The adaption works in part only, not always showing the longest number of the streak. Sometimes is show the shortest.

10x

 

[Jon von der Heyden]

I'm struggling to conjour up anything pretty, hence:

=LOOKUP(9.99E+307,-IF(LEN(-MID(SUBSTITUTE(" "&A1," ",REPT(" ",255)),255*{1;2;3;4;5;6;7;8;9},255))[B][COLOR="Red"]>=5[/COLOR][/B],-MID(SUBSTITUTE(" "&A1," ",REPT(" ",255)),255*{1;2;3;4},255),NA()))

Note the greater than or equal to 5 character length condition highlighted in red.

 

[Christiano]

Yeah, it's better.
Now it shows sometimes. But I noticed that where there are more than one space, it doesn't show anything.
For example, if a line is:
111111(space)5(space)666 --- it works

If the line is:
4(space)(space)6(space)(space)9(space)(space)(space)7777777
------ it doesn't work.

Also, the formula looks a bit complicated. Would it make it easier for you if I only request to show the longest number from a row ?

Ex: 5 7 90 105 66666666 ---- only 66666666 i want to remain.

Thanks again for your time,

 

[Gettingbetter]

try

=RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1," ","|", LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

Cheers
GB

 

[Gettingbetter]

oops, mine wont work if there are no spaces, so try

=IF(LEN(A1)=LEN(SUBSTITUTE(A1," ","")),A1,MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,255))

Cheers
GB

 

[Christiano]

Again is not working

And I have a huge 500 rows sheet than needs adjustment :D