Extracting text/numbers form varying source data

**Sybie** · 01-14-2013, 11:35 AM

Hello all

I am looking to extract 3 pieces of data from source data that can look like this (but is not excluded to it, but if it is anything else, I will be able to change the formula myself):
LP552/ATM506;3;40;001362344;VLANP799;39;119
001362344;VLANP799;39;119;ED
001362344;VLANP799;39;119
...

In a first comumn I need the 001xxx code
In a second column I need the 119:39 code filled in.

The 119 can also be 2 or 4 numbers, as for the 39.
the 001 code is fixed length.

Can anyone direct me towards a non-vba solution?

Thanks in advance for any assistance.

**twiggywales** · 01-14-2013, 11:42 AM

if you upload a book with samples and desired results its easier to help

**Sybie** · 01-15-2013, 03:29 AM

Post deleted as its content is now irrelevant

**Sybie** · 01-15-2013, 06:43 AM

MID($R50;FIND("001";$R50)+0;9
resolves the 001code
1 problem down :-)

**Sybie** · 01-15-2013, 07:18 AM

I updated the attachment, but is giving me problems to upload, so I am trying
Book1.xlsx

For the second problem, I am looking to do something like this:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID($R76;FIND("VLANP";$R76)+0;100);";";"#";1);";";"|";1);";";"@";1)
Replacing the ";" with other symbols and extracting the data like this