Truncating text based on various punctuations

K

kpieper876

New Member
Joined
Aug 12, 2011
Messages
13
I have the following in cell A1:

optimized-by.rubiconproject.com/a/8191/13252

I need to remove everything from the left of the second decimal from the right:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))<2,A1,RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1,".",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,".",""))-1))))

results in this:
rubiconproject.com/a/8191/13252

Now I need to remove everything after the first "/" including the "/":
=IF(FIND("/",A1)>0,LEFT(A9,FIND("/",A1)-1))

Problem is, I want to combine these two formulas into one. AND the second formula looking for the backslash needs to happen AFTER the first formula AND there may not always be a "/" to truncate from.

Thanks for your help.
 
Sort by date Sort by votes
Kpieper876,

Sorry, but I didn't understand. Look at this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">test1.com//345</td><td style="background-color: #C5D9F1;;">test1.com</td><td style=";">test1.com//345</td><td style="background-color: #C5D9F1;;">test1.com</td><td style="text-align: center;;">True</td><td style="background-color: #C5D9F1;;">test1.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">test2.com//dddd</td><td style="background-color: #C5D9F1;;">test2.com</td><td style=";">test2.com//dddd</td><td style="background-color: #C5D9F1;;">test2.com</td><td style="text-align: center;;">True</td><td style="background-color: #C5D9F1;;">test2.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">test4.test3.com/ccc</td><td style="background-color: #C5D9F1;;">test3.com</td><td style=";">test3.com/ccc</td><td style="background-color: #C5D9F1;;">test3.com</td><td style="text-align: center;;">True</td><td style="background-color: #C5D9F1;;">test3.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">optimized-by.rubiconproject.com/a/8191/13252</td><td style="background-color: #C5D9F1;;">rubiconproject.com</td><td style=";">rubiconproject.com/a/8191/13252</td><td style="background-color: #C5D9F1;;">rubiconproject.com</td><td style="text-align: center;;">True</td><td style="background-color: #C5D9F1;;">rubiconproject.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">optimized.-by.rubiconproject.com/a/8191/13252</td><td style="background-color: #C5D9F1;;">rubiconproject.com</td><td style=";">rubiconproject.com/a/8191/13252</td><td style="background-color: #C5D9F1;;">rubiconproject.com</td><td style="text-align: center;;">True</td><td style="background-color: #C5D9F1;;">rubiconproject.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">test.test5.com</td><td style="background-color: #C5D9F1;;">test5.com</td><td style=";">test5.com</td><td style="text-align: right;background-color: #C5D9F1;;">#VALOR!</td><td style="text-align: center;;"></td><td style="background-color: #C5D9F1;;">test5.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">test6.com</td><td style="background-color: #C5D9F1;;">test6.com</td><td style=";">test6.com</td><td style="text-align: right;background-color: #C5D9F1;;">#VALOR!</td><td style="text-align: center;;"></td><td style="background-color: #C5D9F1;;">test6.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="font-weight: bold;text-align: center;color: #FF0000;;">data</td><td style="font-weight: bold;text-align: center;color: #FF0000;;">my formula</td><td style="font-weight: bold;text-align: center;color: #FF0000;;">your formula1</td><td style="font-weight: bold;text-align: center;color: #FF0000;;">your formula2</td><td style="font-weight: bold;text-align: center;color: #FF0000;;">verification</td><td style="font-weight: bold;text-align: center;color: #FF0000;;">P45cal function</td><td style="font-weight: bold;text-align: center;color: #FF0000;;">verification</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 #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">FIND(<font color="Green">"/",A1</font>)</font>),SUBSTITUTE(<font color="Red">IF(<font color="Green">LEN(<font color="Purple">A1</font>)-LEN(<font color="Purple">SUBSTITUTE(<font color="Teal">A1,".",""</font>)</font>)-1,MID(<font color="Purple">A1,FIND(<font color="Teal">CHAR(<font color="#FF00FF">10</font>),SUBSTITUTE(<font color="#FF00FF">A1,".",CHAR(<font color="Navy">10</font>),LEN(<font color="Navy">A1</font>)-LEN(<font color="Navy">SUBSTITUTE(<font color="Blue">A1,".",""</font>)</font>)-1</font>)</font>)+1,1000</font>),A1</font>),RIGHT(<font color="Green">A1,LEN(<font color="Purple">A1</font>)-FIND(<font color="Purple">"/",A1</font>)+1</font>),""</font>),IF(<font color="Red">LEN(<font color="Green">A1</font>)-LEN(<font color="Green">SUBSTITUTE(<font color="Purple">A1,".",""</font>)</font>)-1,MID(<font color="Green">A1,FIND(<font color="Purple">CHAR(<font color="Teal">10</font>),SUBSTITUTE(<font color="Teal">A1,".",CHAR(<font color="#FF00FF">10</font>),LEN(<font color="#FF00FF">A1</font>)-LEN(<font color="#FF00FF">SUBSTITUTE(<font color="Navy">A1,".",""</font>)</font>)-1</font>)</font>)+1,1000</font>),A1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=IF(<font color="Blue">LEN(<font color="Red">A1</font>)-LEN(<font color="Red">SUBSTITUTE(<font color="Green">A1,".",""</font>)</font>)<2,A1,RIGHT(<font color="Red">A1,LEN(<font color="Green">A1</font>)-FIND(<font color="Green">CHAR(<font color="Purple">1</font>),SUBSTITUTE(<font color="Purple">A1,".",CHAR(<font color="Teal">1</font>),LEN(<font color="Teal">A1</font>)-LEN(<font color="Teal">SUBSTITUTE(<font color="#FF00FF">A1,".",""</font>)</font>)-1</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D1</th><td style="text-align:left">=IF(<font color="Blue">FIND(<font color="Red">"/",C1</font>)>0,LEFT(<font color="Red">C1,FIND(<font color="Green">"/",C1</font>)-1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E1</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">B1=D1,"True","False"</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F1</th><td style="text-align:left">=LeaveDomain(<font color="Blue">A1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G1</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">B1=F1,"True","False"</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz
 
Upvote 0

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sorry again, I forgot this "Also, what is the reference to column e?":

E1 - =LEN(A1)-LEN(SUBSTITUTE(A1,".",""))-1

Markmzz
 
Upvote 0
I used this formula:

=IF(ISNUMBER(FIND("/",A1)),SUBSTITUTE(IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))-1,MID(A1,FIND(CHAR(10),SUBSTITUTE(A1,".",CHAR(10),LEN(A1)-LEN(SUBSTITUTE(A1,".",""))-1))+1,1000),A1),RIGHT(A1,LEN(A1)-FIND("/",A1)+1),""),IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))-1,MID(A1,FIND(CHAR(10),SUBSTITUTE(A1,".",CHAR(10),LEN(A1)-LEN(SUBSTITUTE(A1,".",""))-1))+1,1000),A1))

and most of the domains worked, with these exceptions (not sure how to insert an excel table in here):
excel.png

Also, still not sure how to implement the user defined function.
 
Upvote 0
kpieper876 said:
I used this formula:

=IF(ISNUMBER(FIND("/",A1)),SUBSTITUTE(IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))-1,MID(A1,FIND(CHAR(10),SUBSTITUTE(A1,".",CHAR(10),LEN(A1)-LEN(SUBSTITUTE(A1,".",""))-1))+1,1000),A1),RIGHT(A1,LEN(A1)-FIND("/",A1)+1),""),IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))-1,MID(A1,FIND(CHAR(10),SUBSTITUTE(A1,".",CHAR(10),LEN(A1)-LEN(SUBSTITUTE(A1,".",""))-1))+1,1000),A1))

and most of the domains worked, with these exceptions (not sure how to insert an excel table in here):

Also, still not sure how to implement the user defined function.
Click to expand...

Kpieper876,

Try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">media2.legacy.com/adiframe/3.0/5306.1/1369112/0/-1/size</td><td style="background-color: #C5D9F1;;">legacy.com</td><td style=";">media2.legacy.com</td><td style="background-color: #C5D9F1;;">legacy.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">media2.legacy.com/adiframe/3.0/5306.1/1369112/0/-1</td><td style="background-color: #C5D9F1;;">legacy.com</td><td style=";">media2.legacy.com</td><td style="background-color: #C5D9F1;;">legacy.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">cyclops.untd.com/adstream_sx.ads/eow.com/netzero/free/folder/content</td><td style="background-color: #C5D9F1;;">untd.com</td><td style=";">cyclops.untd.com</td><td style="background-color: #C5D9F1;;">untd.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">view.atdmt.com/ntb/iview/265266531/direct;;wi.160;hi.600/01</td><td style="background-color: #C5D9F1;;">atdmt.com</td><td style=";">view.atdmt.com</td><td style="background-color: #C5D9F1;;">atdmt.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">%20i-am-bored.com</td><td style="background-color: #C5D9F1;;">%20i-am-bored.com</td><td style=";">%20i-am-bored.com</td><td style="background-color: #C5D9F1;;">%20i-am-bored.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">cyclops.prod.untd.com/adstream_sx.ads/eow.com/netzero/free/folder/content</td><td style="background-color: #C5D9F1;;">untd.com</td><td style=";">cyclops.prod.untd.com</td><td style="background-color: #C5D9F1;;">untd.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">media2.legacy.com/adiframe/3.0/5306.1</td><td style="background-color: #C5D9F1;;">legacy.com</td><td style=";">media2.legacy.com</td><td style="background-color: #C5D9F1;;">legacy.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">at.atwoal.com/adiframe/3.0/5113.1/221794/0/-1</td><td style="background-color: #C5D9F1;;">atwoal.com</td><td style=";">at.atwoal.com</td><td style="background-color: #C5D9F1;;">atwoal.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">174.34.166.235</td><td style="background-color: #C5D9F1;;">166.235</td><td style=";">174.34.166.235</td><td style="background-color: #C5D9F1;;">166.235</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">view.atdmt.com/apm/iview/133012882/direct;wi.160;hi.600/01</td><td style="background-color: #C5D9F1;;">atdmt.com</td><td style=";">view.atdmt.com</td><td style="background-color: #C5D9F1;;">atdmt.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">view.atdmt.com/apm/iview/112458055/direct;wi.160;hi.600/01</td><td style="background-color: #C5D9F1;;">atdmt.com</td><td style=";">view.atdmt.com</td><td style="background-color: #C5D9F1;;">atdmt.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">test1.com//345</td><td style="background-color: #C5D9F1;;">test1.com</td><td style=";">test1.com</td><td style="background-color: #C5D9F1;;">test1.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">test2.com//dddd</td><td style="background-color: #C5D9F1;;">test2.com</td><td style=";">test2.com</td><td style="background-color: #C5D9F1;;">test2.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">test4.test3.com/ccc</td><td style="background-color: #C5D9F1;;">test3.com</td><td style=";">test4.test3.com</td><td style="background-color: #C5D9F1;;">test3.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">optimized-by.rubiconproject.com/a/8191/13252</td><td style="background-color: #C5D9F1;;">rubiconproject.com</td><td style=";">optimized-by.rubiconproject.com</td><td style="background-color: #C5D9F1;;">rubiconproject.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">optimized.-by.rubiconproject.com/a/8191/13252</td><td style="background-color: #C5D9F1;;">rubiconproject.com</td><td style=";">optimized.-by.rubiconproject.com</td><td style="background-color: #C5D9F1;;">rubiconproject.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">test.test5.com</td><td style="background-color: #C5D9F1;;">test5.com</td><td style=";">test.test5.com</td><td style="background-color: #C5D9F1;;">test5.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">test6.com</td><td style="background-color: #C5D9F1;;">test6.com</td><td style=";">test6.com</td><td style="background-color: #C5D9F1;;">test6.com</td><td style="text-align: center;;">True</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="font-weight: bold;text-align: center;color: #FF0000;;">data</td><td style="font-weight: bold;text-align: center;color: #FF0000;;">my formula</td><td style="font-weight: bold;text-align: center;color: #FF0000;;">your formula2</td><td style="font-weight: bold;text-align: center;color: #FF0000;;">your formula1</td><td style="font-weight: bold;text-align: center;color: #FF0000;;">verification</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet21</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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">FIND(<font color="Green">"/",A1</font>)</font>),IF(<font color="Red">LEN(<font color="Green">LEFT(<font color="Purple">A1,FIND(<font color="Teal">"/",A1</font>)-1</font>)</font>)-LEN(<font color="Green">SUBSTITUTE(<font color="Purple">LEFT(<font color="Teal">A1,FIND(<font color="#FF00FF">"/",A1</font>)-1</font>),".",""</font>)</font>)-1,MID(<font color="Green">LEFT(<font color="Purple">A1,FIND(<font color="Teal">"/",A1</font>)-1</font>),FIND(<font color="Purple">CHAR(<font color="Teal">10</font>),SUBSTITUTE(<font color="Teal">LEFT(<font color="#FF00FF">A1,FIND(<font color="Navy">"/",A1</font>)-1</font>),".",CHAR(<font color="#FF00FF">10</font>),LEN(<font color="#FF00FF">LEFT(<font color="Navy">A1,FIND(<font color="Blue">"/",A1</font>)-1</font>)</font>)-LEN(<font color="#FF00FF">SUBSTITUTE(<font color="Navy">LEFT(<font color="Blue">A1,FIND(<font color="Red">"/",A1</font>)-1</font>),".",""</font>)</font>)-1</font>)</font>)+1,1000</font>),LEFT(<font color="Green">A1,FIND(<font color="Purple">"/",A1</font>)-1</font>)</font>),IF(<font color="Red">LEN(<font color="Green">A1</font>)-LEN(<font color="Green">SUBSTITUTE(<font color="Purple">A1,".",""</font>)</font>)-1,MID(<font color="Green">A1,FIND(<font color="Purple">CHAR(<font color="Teal">10</font>),SUBSTITUTE(<font color="Teal">A1,".",CHAR(<font color="#FF00FF">10</font>),LEN(<font color="#FF00FF">A1</font>)-LEN(<font color="#FF00FF">SUBSTITUTE(<font color="Navy">A1,".",""</font>)</font>)-1</font>)</font>)+1,1000</font>),A1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=IFERROR(<font color="Blue">IF(<font color="Red">FIND(<font color="Green">"/",A1</font>)>0,LEFT(<font color="Green">A1,FIND(<font color="Purple">"/",A1</font>)-1</font>)</font>),A1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D1</th><td style="text-align:left">=IF(<font color="Blue">LEN(<font color="Red">C1</font>)-LEN(<font color="Red">SUBSTITUTE(<font color="Green">C1,".",""</font>)</font>)<2,C1,RIGHT(<font color="Red">C1,LEN(<font color="Green">C1</font>)-FIND(<font color="Green">CHAR(<font color="Purple">1</font>),SUBSTITUTE(<font color="Purple">C1,".",CHAR(<font color="Teal">1</font>),LEN(<font color="Teal">C1</font>)-LEN(<font color="Teal">SUBSTITUTE(<font color="#FF00FF">C1,".",""</font>)</font>)-1</font>)</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E1</th><td style="text-align:left">=IF(<font color="Blue">B1=D1,"True","False"</font>)</td></tr></tbody></table></td></tr></table><br />

To post your examples, put borders around the range of the cells of your examples.

Then copy the range, and paste them here.


Markmzz
 
Upvote 0
Rows five and nine:


%20i-am-bored.com
174.34.166.235

result is like this:

%20i-am-bored.com
166.235

Needs to be: (preserve IP addresses and remove any encoding)
i-am-bored.com
174.34.166.235

I suppose there is no way to make a one column formula with this beast?
 
Upvote 0
kpieper876 said:
Rows five and nine:


%20i-am-bored.com
174.34.166.235

result is like this:

%20i-am-bored.com
166.235

Needs to be: (preserve IP addresses and remove any encoding)
i-am-bored.com
174.34.166.235

I suppose there is no way to make a one column formula with this beast?
Click to expand...
Kpieper876,

I think you have to define new restrictions, since my formula follows your initial restrictions and does not solve your problem.

Furthermore, I believe that only by using VBA your problem can be resolved. Maybe, with a variation of the code of P45cal.

Markmzz
 
Upvote 0
kpieper876 said:
Ok, thanks. This has gotten me 95% there.
Click to expand...

Try this for IP addresses:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">174.34.166.235/teste/teste2</td><td style="background-color: #C5D9F1;;">174.34.166.235</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">174.34.166.235</td><td style="background-color: #C5D9F1;;">174.34.166.235</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="font-weight: bold;text-align: center;color: #FF0000;;">data</td><td style="font-weight: bold;text-align: center;color: #FF0000;;">my formula</td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet25</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: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;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: #E0E0F0;color: #161120">B19</th><td style="text-align:left">=IF(<font color="Blue">ISNUMBER(<font color="Red">FIND(<font color="Green">"/",A19</font>)</font>),IF(<font color="Red">AND(<font color="Green">LEN(<font color="Purple">LEFT(<font color="Teal">A19,FIND(<font color="#FF00FF">"/",A19</font>)-1</font>)</font>)-LEN(<font color="Purple">SUBSTITUTE(<font color="Teal">LEFT(<font color="#FF00FF">A19,FIND(<font color="Navy">"/",A19</font>)-1</font>),".",""</font>)</font>)-1,ISERR(<font color="Purple">SUBSTITUTE(<font color="Teal">LEFT(<font color="#FF00FF">A19,FIND(<font color="Navy">"/",A19</font>)-1</font>),".",""</font>)*1</font>)</font>),MID(<font color="Green">LEFT(<font color="Purple">A19,FIND(<font color="Teal">"/",A19</font>)-1</font>),FIND(<font color="Purple">CHAR(<font color="Teal">10</font>),SUBSTITUTE(<font color="Teal">LEFT(<font color="#FF00FF">A19,FIND(<font color="Navy">"/",A19</font>)-1</font>),".",CHAR(<font color="#FF00FF">10</font>),LEN(<font color="#FF00FF">LEFT(<font color="Navy">A19,FIND(<font color="Blue">"/",A19</font>)-1</font>)</font>)-LEN(<font color="#FF00FF">SUBSTITUTE(<font color="Navy">LEFT(<font color="Blue">A19,FIND(<font color="Red">"/",A19</font>)-1</font>),".",""</font>)</font>)-1</font>)</font>)+1,1000</font>),LEFT(<font color="Green">A19,FIND(<font color="Purple">"/",A19</font>)-1</font>)</font>),IF(<font color="Red">AND(<font color="Green">LEN(<font color="Purple">A19</font>)-LEN(<font color="Purple">SUBSTITUTE(<font color="Teal">A19,".",""</font>)</font>)-1,ISERR(<font color="Purple">SUBSTITUTE(<font color="Teal">A19,".",""</font>)*1</font>)</font>),MID(<font color="Green">A19,FIND(<font color="Purple">CHAR(<font color="Teal">10</font>),SUBSTITUTE(<font color="Teal">A19,".",CHAR(<font color="#FF00FF">10</font>),LEN(<font color="#FF00FF">A19</font>)-LEN(<font color="#FF00FF">SUBSTITUTE(<font color="Navy">A19,".",""</font>)</font>)-1</font>)</font>)+1,1000</font>),A19</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Help with Formula
Replies
6
Views
328
Cubist
Cubist
M
  • Solved
FORMULA TO PASTE SPECIAL (VALUE)
Replies
5
Views
92
Joe4
Joe4
V
  • Question Question
VBA help - find string between 2nd and 3rd occurrence of a character.
Replies
3
Views
380
DanteAmor
DanteAmor
C
  • Solved
Variable number formatting formula
Replies
7
Views
231
Joe4
Joe4
K
  • Question Question
Modify COUNTIF Formula to count inside parenthesis
Replies
7
Views
245
Fluff
Fluff

Forum statistics

Threads
1,225,156
Messages
6,183,222
Members
453,152
Latest member
ChrisMd

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top