Assistance needed with formula.

[pujo]

Trying to create separate formulas to extract substrings from a cell.
My application exports to a csv and combines data from different areas, putting into one cell.

I would like to split the data like this.

Example: {A1} = 192.168.1.1bi.OPC.1:Analog Item '123-10.5555' Bits 2
{B1} = 192.168.1.1
{C1} = obi.OPC.1
{D1} = Analog Item
{E1} = '123-10.2222'
{F1} = Bits 2

I was able to get the first two substrings but having trouble getting the remaining.
Formula: {B1} =LEFT(A1, SEARCH(":",A1)-1) 'IP Address
{C1} =MID(A1, SEARCH(":",A1) + 1, SEARCH(":",A4,SEARCH(":",A1)+1) - SEARCH(":",A1) - 1)

Appreciate any assistance !!

PuJo

 

[pujo]

I am looking up the FILTERXML function to try and learn how to use it.
Any recommended literature?

 

[Fluff]

Any recommended literature?

Afraid not, or at least not that I'm aware of as far as Excel is concerned.

 

[pujo]

@Fluff

Thanks for the assistance this morning with the filterxml function.
For the last couple hours I have been trying to figure it out so that I can add characters to the formula.

Would you mind explaining the theory of operation for the formula?

How would I go about adding the curly brackets along with the < symbol similar to the way you added the "<","&lt;"
'333-10.Barometer{-1:100}' should be in one cell but its splitting after the colon.

Thanks again!

SCADA_DATA.xlsb
	A	B	C	D	E	F	G
1	10.10.1.10:asi.OPC.1:Analog Item '333-10.ActualInterval<C1>' Bit 2	10.10.1.10	asi.OPC.1	Analog Item	'333-10.ActualInterval<C1>'	Bit 2
2	10.10.1.10:asi.OPC.1:Analog Item '333-10.Barometer{-1:100}' Bit 5	10.10.1.10	asi.OPC.1	Analog Item	'333-10.Barometer}-1	100}'	Bit 5
Sheet5

Cell Formulas
Range	Formula
B2:G2,B1:F1	B1	=FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"{","}"),"<","&lt;")," '",":'"),"' ","':"),":","</m><m>")&"</m></k>","//m["&COLUMNS($B1:B1)&"]")

 

[Fluff]

Will the : in the {} always be followed by a 1

 

[pujo]

Yes, every bracket in the worksheet is the same. Brackets are only used for the Barometer tags, i.e. '333-27.Barometer{-1:100}'

 

[Fluff]

In that case use

=FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"<","&lt;"),":1","&#58;1")," '",":'"),"' ","':"),":","</m><m>")&"</m></k>","//m["&COLUMNS($B1:B1)&"]")

 

[pujo]

Perfect!

Would you mind explaining the theory of operation for the formula?

 

[Fluff]

Unfortunately it's not that easy to explain.

 

[pujo]

Gotcha!

I appreciate all your time helping me out with this.
It's going to save me hours of work!

 

[Fluff]

You're welcome & thanks for the feedback.