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

 

[Jon von der Heyden]

In your recent exhibits there are no strings with numbers of 5 characters or more. Thus that LOOKUP method won't do the trick.

The MAX method however:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">6 89 555 7 1 2655</td><td style="text-align: right;;">2655</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">10 500 2000</td><td style="text-align: right;;">2000</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">67 10000 15 6</td><td style="text-align: right;;">10000</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">500 15 5</td><td style="text-align: right;;">500</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">{=MAX(<font color="Blue">--MID(<font color="Red">SUBSTITUTE(<font color="Green">" "&TRIM(<font color="Purple">A1</font>)," ",REPT(<font color="Purple">" ",255</font>)</font>),255*ROW(<font color="Green">INDIRECT(<font color="Purple">"1:"&LEN(<font color="Teal">TRIM(<font color="#FF00FF">A1</font>)</font>)-LEN(<font color="Teal">SUBSTITUTE(<font color="#FF00FF">TRIM(<font color="Navy">A1</font>)," ",""</font>)</font>)+1</font>)</font>),255</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Works!

The LOOKUP will work provided you have 5 character length numbers:
<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">45 87653 45</td><td style="text-align: right;;">87653</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">89 26 785 98765</td><td style="text-align: right;;">98765</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">18975 6 648</td><td style="text-align: right;;">18975</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">654 12345 56446</td><td style="text-align: right;;">56446</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B1</th><td style="text-align:left">=LOOKUP(<font color="Blue">9.99E+307,-IF(<font color="Red">LEN(<font color="Green">--MID(<font color="Purple">SUBSTITUTE(<font color="Teal">" "&TRIM(<font color="#FF00FF">A1</font>)," ",REPT(<font color="#FF00FF">" ",255</font>)</font>),255*{1;2;3;4;5;6;7;8;9},255</font>)</font>)>=5,-MID(<font color="Green">SUBSTITUTE(<font color="Purple">" "&TRIM(<font color="Teal">A1</font>)," ",REPT(<font color="Teal">" ",255</font>)</font>),255*{1;2;3;4;5;6;7;8;9},255</font>),NA(<font color="Green"></font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Additionally these formula trim the text strings for you.

 

[Christiano]

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

This one works, I didn;t know before the 'CTRL+Shift+enter' mode

It chooses the max values from the whole string

 

[Christiano]

In your recent exhibits there are no strings with numbers of 5 characters or more. Thus that LOOKUP method won't do the trick.

The MAX method however:

Excel 2010<TABLE style="BORDER-RIGHT: #bbb 1px solid; BORDER-TOP: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BORDER-BOTTOM: #bbb 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5" width=25><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #dae7f5; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>6 89 555 7 1 2655</TD><TD style="TEXT-ALIGN: right">2655</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD>10 500 2000</TD><TD style="TEXT-ALIGN: right">2000</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD>67 10000 15 6</TD><TD style="TEXT-ALIGN: right">10000</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD>500 15 5</TD><TD style="TEXT-ALIGN: right">500</TD></TR></TBODY></TABLE>

Sheet1​

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Array Formulas<TABLE style="BORDER-RIGHT: #bbb 1px solid; BORDER-TOP: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BORDER-BOTTOM: #bbb 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #dae7f5"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #dae7f5" width=10>B1</TH><TD style="TEXT-ALIGN: left">{=MAX(--MID(SUBSTITUTE(" "&TRIM(A1)," ",REPT(" ",255)),255*ROW(INDIRECT("1:"&LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1)," ",""))+1)),255))}</TD></TR></TBODY></TABLE>Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself
</TD></TR></TBODY></TABLE>

Works!

The LOOKUP will work provided you have 5 character length numbers:
Excel 2010<TABLE style="BORDER-RIGHT: #bbb 1px solid; BORDER-TOP: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BORDER-BOTTOM: #bbb 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all><COLGROUP><COL style="BACKGROUND-COLOR: #dae7f5" width=25><COL><COL></COLGROUP><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #dae7f5; TEXT-ALIGN: center"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">1</TD><TD>45 87653 45</TD><TD style="TEXT-ALIGN: right">87653</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">2</TD><TD>89 26 785 98765</TD><TD style="TEXT-ALIGN: right">98765</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">3</TD><TD>18975 6 648</TD><TD style="TEXT-ALIGN: right">18975</TD></TR><TR><TD style="COLOR: #161120; TEXT-ALIGN: center">4</TD><TD>654 12345 56446</TD><TD style="TEXT-ALIGN: right">56446</TD></TR></TBODY></TABLE>

Sheet1​

<TABLE style="BORDER-RIGHT: black 2px solid; PADDING-RIGHT: 0.4em; BORDER-TOP: black 2px solid; PADDING-LEFT: 0.4em; PADDING-BOTTOM: 0.4em; BORDER-LEFT: black 2px solid; PADDING-TOP: 0.4em; BORDER-BOTTOM: black 2px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff" cellPadding=2 rules=all width="85%"><TBODY><TR><TD style="PADDING-RIGHT: 6px; PADDING-LEFT: 6px; PADDING-BOTTOM: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-RIGHT: #bbb 1px solid; BORDER-TOP: #bbb 1px solid; BORDER-LEFT: #bbb 1px solid; BORDER-BOTTOM: #bbb 1px solid; BORDER-COLLAPSE: collapse; BACKGROUND-COLOR: #ffffff; TEXT-ALIGN: center" cellPadding=2 rules=all width="100%"><THEAD><TR style="COLOR: #161120; BACKGROUND-COLOR: #dae7f5"><TH width=10>Cell</TH><TH style="PADDING-LEFT: 5px; TEXT-ALIGN: left">Formula</TH></TR></THEAD><TBODY><TR><TH style="COLOR: #161120; BACKGROUND-COLOR: #dae7f5" width=10>B1</TH><TD style="TEXT-ALIGN: left">=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()))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

I CONFIRM!!!! WORKS FLAWLESSLY !

SORRY FOR THE UPSET I CAUSED YOU, AS MY SIGNATURE SHOWS THIS THREAD IS MY FIRST AND THE BEGINNING IS HARD SOMETIMES.
I HAVE HEADACHE ALREADY OF IT, I'M GLAD IT'S DONE.

THANKS EVERYONE!!

 

[Jon von der Heyden]

Whew! I thought I was going mad.

Hope to see you here again.