Help to remove some numbers

J

Jamesetaylor2

New Member
Joined
Mar 17, 2022
Messages
9
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Remove everything after the second | and before the third |
including the third | to look like this below

Example before 1|10|0519 1|00000119.TIF

Completed example 1|10|00000119.TIF


The file is in Notepad. We have to return it to notepad if skipping excel helps. See the original (sampling):

BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|1|00000001.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|2|00000002.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|3|00000003.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|4|00000004.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|5|00000005.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|6|00000006.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|7|00000007.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|8|00000008.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|9|00000009.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|10|00000010.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|11|00000011.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|12|00000012.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|13|00000013.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|14|00000014.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|15|00000015.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|16|00000016.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|17|00000017.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|18|00000018.TIF
 

Attachments

  • x.PNG
    x.PNG
    54.5 KB · Views: 13
Last edited by a moderator:

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
Jamesetaylor2 said:
Remove everything after the second | and before the third |
including the third | to look like this below

Example before 1|10|0519 1|00000119.TIF

Completed example 1|10|00000119.TIF


The file is in Notepad. We have to return it to notepad if skipping excel helps. See the original (sampling):

BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|1|00000001.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|2|00000002.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|3|00000003.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|4|00000004.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|5|00000005.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|6|00000006.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|7|00000007.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|8|00000008.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|9|00000009.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|10|00000010.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|11|00000011.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|12|00000012.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|13|00000013.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|14|00000014.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|15|00000015.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|16|00000016.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|17|00000017.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|18|00000018.TIF
Click to expand...
Hi, this looks like a good one to try. Use the Upload Mini-Sheet button to upload some untouched sample data or supply a link to untouched sample data and I will try to come up with a solution.
 
Upvote 0
what do these look like AFTER the required text has been removed
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|1|00000001.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|2|00000002.TIF

The sample you provided AND the screenshot don't seem to have any similarities...so makes it a bit hard to provide a solution
 
Upvote 0
Would be nice to know if what's being asked for is a vba or formula solution. If the former, then perhaps
left(str,instr(InStr(1, str, "|")+1,str,"|")) & mid(str,instrrev(str,"|"))
where str = "1|10|0519 1|00000119.TIF"
Jamesetaylor2 said:
Example before 1|10|0519 1|00000119.TIF
Click to expand...
 
Upvote 0
Micron said:
Would be nice to know if what's being asked for is a vba or formula solution.
Click to expand...
Agreed. :)

Jamesetaylor2 said:
Remove everything after the second | and before the third |
including the third |
Click to expand...
Macro & formula suggestions, assuming data in column D, starting in row 2:

VBA Code: 
Sub RemoveThirdSection()
  Dim a As Variant, Bits As Variant
  Dim i As Long
  
  a = Range("D2", Range("D" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    Bits = Split(a(i, 1), "|")
    Bits(2) = "@"
    a(i, 1) = Join(Filter(Bits, "@", False), "|")
  Next i
  Range("F2").Resize(UBound(a)).Value = a 'Change F2 to D2 to over-write original data
End Sub

Jamesetaylor2.xlsm
DEF
1
2BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|1|00000001.TIFBOX 080|AD, AT|VOLUME 1|1|00000001.TIFBOX 080|AD, AT|VOLUME 1|1|00000001.TIF
3BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|2|00000002.TIFBOX 080|AD, AT|VOLUME 1|2|00000002.TIFBOX 080|AD, AT|VOLUME 1|2|00000002.TIF
4BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|3|00000003.TIFBOX 080|AD, AT|VOLUME 1|3|00000003.TIFBOX 080|AD, AT|VOLUME 1|3|00000003.TIF
5BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|4|00000004.TIFBOX 080|AD, AT|VOLUME 1|4|00000004.TIFBOX 080|AD, AT|VOLUME 1|4|00000004.TIF
6BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|5|00000005.TIFBOX 080|AD, AT|VOLUME 1|5|00000005.TIFBOX 080|AD, AT|VOLUME 1|5|00000005.TIF
7BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|6|00000006.TIFBOX 080|AD, AT|VOLUME 1|6|00000006.TIFBOX 080|AD, AT|VOLUME 1|6|00000006.TIF
8BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|7|00000007.TIFBOX 080|AD, AT|VOLUME 1|7|00000007.TIFBOX 080|AD, AT|VOLUME 1|7|00000007.TIF
9BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|8|00000008.TIFBOX 080|AD, AT|VOLUME 1|8|00000008.TIFBOX 080|AD, AT|VOLUME 1|8|00000008.TIF
10BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|9|00000009.TIFBOX 080|AD, AT|VOLUME 1|9|00000009.TIFBOX 080|AD, AT|VOLUME 1|9|00000009.TIF
11BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|10|00000010.TIFBOX 080|AD, AT|VOLUME 1|10|00000010.TIFBOX 080|AD, AT|VOLUME 1|10|00000010.TIF
12BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|11|00000011.TIFBOX 080|AD, AT|VOLUME 1|11|00000011.TIFBOX 080|AD, AT|VOLUME 1|11|00000011.TIF
13BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|12|00000012.TIFBOX 080|AD, AT|VOLUME 1|12|00000012.TIFBOX 080|AD, AT|VOLUME 1|12|00000012.TIF
14BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|13|00000013.TIFBOX 080|AD, AT|VOLUME 1|13|00000013.TIFBOX 080|AD, AT|VOLUME 1|13|00000013.TIF
15BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|14|00000014.TIFBOX 080|AD, AT|VOLUME 1|14|00000014.TIFBOX 080|AD, AT|VOLUME 1|14|00000014.TIF
16BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|15|00000015.TIFBOX 080|AD, AT|VOLUME 1|15|00000015.TIFBOX 080|AD, AT|VOLUME 1|15|00000015.TIF
17BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|16|00000016.TIFBOX 080|AD, AT|VOLUME 1|16|00000016.TIFBOX 080|AD, AT|VOLUME 1|16|00000016.TIF
18BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|17|00000017.TIFBOX 080|AD, AT|VOLUME 1|17|00000017.TIFBOX 080|AD, AT|VOLUME 1|17|00000017.TIF
19BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|18|00000018.TIFBOX 080|AD, AT|VOLUME 1|18|00000018.TIFBOX 080|AD, AT|VOLUME 1|18|00000018.TIF
Sheet1
Cell Formulas
RangeFormula
E2:E19E2=LET(p,FIND("|",D2,FIND("|",D2)+1)+1,REPLACE(D2,p,FIND("|",D2,p)-p+1,""))


Formula assumes you have the LET function. If not you could try these

Excel Formula: 
=REPLACE(D2,FIND("|",D2,FIND("|",D2)+1)+1,FIND("|",D2,FIND("|",D2,FIND("|",D2)+1)+1)-FIND("|",D2,FIND("|",D2)+1)+1+1,"")

Excel Formula: 
=SUBSTITUTE(SUBSTITUTE(TRIM(REPLACE(SUBSTITUTE(SUBSTITUTE(D2," ","#"),"|",REPT(" ",100)),200,100,""))," ","|"),"#"," ")
 
Upvote 0
Hi,

Another formula for non-365:

Book3.xlsx
DE
1
2BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|1|00000001.TIFBOX 080|AD, AT|VOLUME 1|1|00000001.TIF
3BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|2|00000002.TIFBOX 080|AD, AT|VOLUME 1|2|00000002.TIF
4BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|3|00000003.TIFBOX 080|AD, AT|VOLUME 1|3|00000003.TIF
5BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|4|00000004.TIFBOX 080|AD, AT|VOLUME 1|4|00000004.TIF
6BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|5|00000005.TIFBOX 080|AD, AT|VOLUME 1|5|00000005.TIF
7BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|6|00000006.TIFBOX 080|AD, AT|VOLUME 1|6|00000006.TIF
8BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|7|00000007.TIFBOX 080|AD, AT|VOLUME 1|7|00000007.TIF
9BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|8|00000008.TIFBOX 080|AD, AT|VOLUME 1|8|00000008.TIF
10BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|9|00000009.TIFBOX 080|AD, AT|VOLUME 1|9|00000009.TIF
11BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|10|00000010.TIFBOX 080|AD, AT|VOLUME 1|10|00000010.TIF
12BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|11|00000011.TIFBOX 080|AD, AT|VOLUME 1|11|00000011.TIF
13BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|12|00000012.TIFBOX 080|AD, AT|VOLUME 1|12|00000012.TIF
14BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|13|00000013.TIFBOX 080|AD, AT|VOLUME 1|13|00000013.TIF
15BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|14|00000014.TIFBOX 080|AD, AT|VOLUME 1|14|00000014.TIF
16BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|15|00000015.TIFBOX 080|AD, AT|VOLUME 1|15|00000015.TIF
17BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|16|00000016.TIFBOX 080|AD, AT|VOLUME 1|16|00000016.TIF
18BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|17|00000017.TIFBOX 080|AD, AT|VOLUME 1|17|00000017.TIF
19BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|18|00000018.TIFBOX 080|AD, AT|VOLUME 1|18|00000018.TIF
Sheet1042
Cell Formulas
RangeFormula
E2:E19E2=TRIM(LEFT(SUBSTITUTE(D2,"|",REPT(" ",100),2),100))&"|"&TRIM(RIGHT(SUBSTITUTE(D2,"|",REPT(" ",100),3),100))
 
Upvote 0
Jamesetaylor2 said:
Remove everything after the second | and before the third |
including the third | to look like this below

Example before 1|10|0519 1|00000119.TIF

Completed example 1|10|00000119.TIF


The file is in Notepad. We have to return it to notepad if skipping excel helps. See the original (sampling):

BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|1|00000001.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|2|00000002.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|3|00000003.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|4|00000004.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|5|00000005.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|6|00000006.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|7|00000007.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|8|00000008.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|9|00000009.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|10|00000010.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|11|00000011.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|12|00000012.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|13|00000013.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|14|00000014.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|15|00000015.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|16|00000016.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|17|00000017.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|18|00000018.TIF
Click to expand...
This data is from a note pad. The guy has asked my manager to remove the
data after the second "|" and the remove the 3rd "|" We copy and pasted the
data into a excel sheet to have more options for removing the info. Then we have to copy it
back to the note pad and save it.

Example before 1|10|0519 1|00000119.TIF

Completed example 1|10|00000119.TIF



BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|1|00000001.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|2|00000002.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|3|00000003.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|4|00000004.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|5|00000005.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|6|00000006.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|7|00000007.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|8|00000008.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|9|00000009.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|10|00000010.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|11|00000011.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|12|00000012.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|13|00000013.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|14|00000014.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|15|00000015.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|16|00000016.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|17|00000017.TIF
BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|18|00000018.TIF
 
Upvote 0
Jamesetaylor2 said:
This data is from a note pad. The guy has asked my manager to remove the
data after the second "|" and the remove the 3rd "|"
Example before 1|10|0519 1|00000119.TIF
Completed example 1|10|00000119.TIF

BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|1|00000001.TIF
Click to expand...

Your samples Don't match, see red above.

May be you want this:

Book3.xlsx
DE
1
2BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|1|00000001.TIFBOX 080|AD, AT|00000001.TIF
3BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|2|00000002.TIFBOX 080|AD, AT|00000002.TIF
4BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|3|00000003.TIFBOX 080|AD, AT|00000003.TIF
5BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|4|00000004.TIFBOX 080|AD, AT|00000004.TIF
6BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|5|00000005.TIFBOX 080|AD, AT|00000005.TIF
7BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|6|00000006.TIFBOX 080|AD, AT|00000006.TIF
8BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|7|00000007.TIFBOX 080|AD, AT|00000007.TIF
9BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|8|00000008.TIFBOX 080|AD, AT|00000008.TIF
10BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|9|00000009.TIFBOX 080|AD, AT|00000009.TIF
11BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|10|00000010.TIFBOX 080|AD, AT|00000010.TIF
12BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|11|00000011.TIFBOX 080|AD, AT|00000011.TIF
13BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|12|00000012.TIFBOX 080|AD, AT|00000012.TIF
14BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|13|00000013.TIFBOX 080|AD, AT|00000013.TIF
15BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|14|00000014.TIFBOX 080|AD, AT|00000014.TIF
16BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|15|00000015.TIFBOX 080|AD, AT|00000015.TIF
17BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|16|00000016.TIFBOX 080|AD, AT|00000016.TIF
18BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|17|00000017.TIFBOX 080|AD, AT|00000017.TIF
19BOX 080|AD, AT|50-1999-CA-000997-ASAI-MB|VOLUME 1|18|00000018.TIFBOX 080|AD, AT|00000018.TIF
Sheet1042
Cell Formulas
RangeFormula
E2:E19E2=TRIM(LEFT(SUBSTITUTE(D2,"|",REPT(" ",100),2),100))&"|"&TRIM(RIGHT(SUBSTITUTE(D2,"|",REPT(" ",100),5),100))
 
Upvote 0
See attached example. We can put the data in a new excel.

BEFOREAFTERREMOVE
1|10|0519 1|00000119.TIF1|10|00000119.TIFRemove the data after the second "|" and remove the 3rd "|"
Removed 0519 1|
 

Attachments

  • vrc.JPG
    vrc.JPG
    97.5 KB · Views: 3
Upvote 0
See post # 6 and post # 8

Is one of these what you want?
 
Upvote 0
You must log in or register to reply here.

Similar threads

Rob_010101
  • Question
Filter Data?
2
Replies
16
Views
1K
kevin9999
K

Forum statistics

Threads
1,221,848
Messages
6,162,419
Members
451,765
Latest member
craigvan888

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