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

 

[Christiano]

I am still thinking about this issue, and an idea came to my mind. The longest number also means the biggest, correct ?
Of course xxxx will always be greater than xx.
You can use MAX for this formula, now, only problem, that the numbers are in the same cell, and not in separate cells. Also delimited by 1 or multiple (space) characters.

Does this help ?

 

[Jon von der Heyden]

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

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

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.

My first suggestion should do that. Here it is again updated to support multiple spaces:

=MAX(--MID(SUBSTITUTE(" "&TRIM(A1)," ",REPT(" ",255)),255*ROW(INDIRECT("1:"&LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1)),255))

Confirmed with control+shift+enter

 

[Jon von der Heyden]

I am still thinking about this issue, and an idea came to my mind. The longest number also means the biggest, correct ?
Of course xxxx will always be greater than xx.
You can use MAX for this formula, now, only problem, that the numbers are in the same cell, and not in separate cells. Also delimited by 1 or multiple (space) characters.

Does this help ?

See my reply above. I did initially assume you were after the largest #.

However this statement isn't necessarily true should you have a mix of +ve and -ve #'s. For example, -1 is less than 1, yet is 2 characters long versus 1.

 

[Gettingbetter]

oops didnt read the question properly,

If Jons doesnt work,

You could try

=MID(A1,LEN(A1)+1-MATCH(MAX(FIND(" ",(RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1))))&" "))),FIND(" ",(RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1))))&" ")),0),MAX(FIND(" ",(RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1))))&" ")))-1)

Needs to be entered with Ctrl + shift + enter

taken from
http://www.mrexcel.com/forum/showthread.php?t=104761&page=2

 

[chirp]

this is one of those things where a UDF would probably be simpler (and probably faster) than native functions

 

[Christiano]

I keep on thinking, and I made it even easier.
I use the TRIM function alone on the numbers, and now it looks like:

10(space)500(space)20000 ---need to show 20000
67(space)10000(space)15(space)6 ---need to show 10000
50000(space)15(space)5 ---need to show 50000

Also guys, I'm a bit ashame, but I don't know the CTRL SHIFT ENTER trick.
Anyone kind to step-by-step in this?

Thanks

 

[Muhamed Faizal]

Try the following

assuming your data stored in column A and
Formula in A2
=RIGHT(A1,LEN(A1)-(FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))),1)))

 

[Jon von der Heyden]

Have you tried either of my last suggestions?

Also guys, I'm a bit ashame, but I don't know the CTRL SHIFT ENTER trick.
Anyone kind to step-by-step in this?

You don't need CSE if you go with my LOOKUP method.

For the other method:
Copy the formula
Select the cell that you want the result in and hit F2
Paste the formula (Ctrl+V)
Press Ctl+Shift+Enter simultaneously

 

[Christiano]

Nothing

Also the formating of the source cell matters ?
I have numbers made of 13 characters : 1852675234566.
For Excel to display whole nr I format the cells as ZIP code. I seen also that TEXT formating is ok ( displays correctly )

BTW, the numbers in cells are not limited to 3 numbers. There could be 4,5, or even more.

I.E. : 6(space)89(space)5555(space)7(space)1(space)2655(space) - how to show up 5555 ??
And what format of imput data would help you guys most ?

 

[Muhamed Faizal]

Have you tried either of my last suggestions?


You don't need CSE if you go with my LOOKUP method.

For the other method:
Copy the formula
Select the cell that you want the result in and hit F2
Paste the formula (Ctrl+V)
Press Ctl+Shift+Enter simultaneously

Heyden, good thinking.