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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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

I
  • Question
Is it possible to insert/delete rows in a file and make other files automatically update their rows accordingly?
Replies
3
Views
217
jkpieterse
jkpieterse
N
  • Solved
Combine LEFT RIGHT formulas with content from cell with ALT-enter text
Replies
3
Views
648
Fluff
Fluff
N
  • Solved
Autosort then #REF! issue on another sheet. Lock formulas and mirror cells?
Replies
2
Views
375
noveske
N
J
  • Solved
Nested LOOKUP formula with TRIM and FIND
Replies
2
Views
172
janema
J
N
  • Locked
  • Question
Autosort then #REF! issue on another sheet. Lock formulas and mirror cells?
Replies
2
Views
276
Fluff
Fluff

Forum statistics

Threads
1,216,040
Messages
6,128,454
Members
449,455
Latest member
jesski

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