How to extract only numbers between two specific characters

[analyst0503]

Hello,

I have a very specific example where I want to only extract numbers between specific characters and I can't find a formula to do this in bulk. Would be grateful for any help!

As you can see in the example above, I only want to keep a specific number. These are dates that will be 4 digits and therefore I would like to only extract those. They are separated by a pipe symbol. The pattern is always 4 digits between pipe symbols.

Thanks in advance!

 

[alansidman]

Can you provide more than one example so that we know if the locations vary in the PIPE. Perhaps a half dozen examples.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D
4
5	Days|and|1989|this is an example|200	1989
6	Days|and|1989|this is an example|2010	1989	2010
7
List

Cell Formulas
Range	Formula
B5,B6:C6	B5	=TRANSPOSE(FILTERXML("<k><m>"&SUBSTITUTE(A5&"|","|","</m><m>")&"</m></k>","//m[string-length()=4and number()]"))

 

[navic]

How about

=TRIM(LEFT(SUBSTITUTE(MID(SUBSTITUTE($A2;"|";REPT(" ";LEN($A2)));LEN($A2)*2;LEN($A2));"|";REPT(" ";255));255))

 

[analyst0503]

Thank you! I'll give them a try and report back on the progress.

I have another instance where the years are |1878-98|, is there a way to tackle this?

 

[Fluff]

Can you post some samples of your data & expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[alansidman]

If your information that you wish to extract is located only in the third set of Pipe delimiters then Power Query will work regardless of the data in that third set.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Extracted Text Between Delimiters" = Table.TransformColumns(Source, {{"Column1", each Text.BetweenDelimiters(_, "|", "|", 1, 0), type text}})
in
    #"Extracted Text Between Delimiters"

 

[analyst0503]

Thank you all for your responses. Unfortunately, I'm unable to share the actual data but happy to add some screenshots.

Here are some of the samples of things that I'm trying to extract:

These are some patterns that I'm noticing. There will be multiple instances where there are numbers between pipes so these are some examples of the patterns.

Really appreciate everyone's help!

 

[analyst0503]

How about

+Fluff 1.xlsm
	A	B	C	D
4
5	Days|and|1989|this is an example|200	1989
6	Days|and|1989|this is an example|2010	1989	2010
7
List

Cell Formulas
Range	Formula
B5,B6:C6	B5	=TRANSPOSE(FILTERXML("<k><m>"&SUBSTITUTE(A5&"|","|","</m><m>")&"</m></k>","//m[string-length()=4and number()]"))

The Transpose formula looks great and I would love that kind of result but it doesn't work for me. Could it be that I'm doing something wrong? I tried doing both Enter and Ctrl+Shift+Enter but it only found two results.

 

[Fluff]

That data is nothing like your original request, so the formula I suggested will not work.
Further more there is no consistency in the data, so not sure how to do it.