Assistance needed with formula.

P

pujo

Well-known Member
Joined
Feb 19, 2009
Messages
708
Office Version
  1. 2019
  2. 2013
Platform
  1. Windows
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.1:eek:bi.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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Sort by date Sort by votes
How about
++Fluff.xlsm
ABCDEF
1
2192.168.1.1:obi.OPC.1:Analog Item '123-10.5555' Bits 2192.168.1.1obi.OPC.1Analog Item'123-10.5555'Bits 2
Data
Cell Formulas
RangeFormula
B2:F2B2=FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A2," '",":'"),"' ","':"),":","</m><m>")&"</m></k>","//m["&COLUMNS($B2:B2)&"]")
 
Upvote 0
here's a way with one helper- e1

Book1
ABCDEFG
1192.168.1.1:eek:bi.OPC.1:Analog Item '123-10.5555' Bits 2192.168.1.1eekbi.OPC.1Analog Item '123-10.5555' Bits 2123-10.5555Bits 2
Sheet1
Cell Formulas
RangeFormula
B1:E1B1=TRIM(MID(SUBSTITUTE($A1,":",REPT(" ",LEN($A1))),COLUMNS($A:A)*LEN($A1)-(LEN($A1)-1),LEN($A1)))
F1:G1F1=TRIM(MID(SUBSTITUTE($E1,"'",REPT(" ",LEN($E1))),COLUMNS($E:F)*LEN($E1)-(LEN($E1)-1),LEN($E1)))
 
Upvote 0
ExceLoki's solution has an error in his interpretation of your original data. In the original post, the : o has been turned into a character. He has inserted : eek : instead of the : o in the original data.
For the B1 row, it will only be put in range B1:D1
For his F1 row, you place it in column E1 and would change move all the cell references back one column. Then copy it to F1.
 
Upvote 0
Thanks guys!
This is exactly what I needed. Just have to put my IFERROR and I am good to go!

@EcelLoki
I have never seen the FILTERXML function, NICE!:cool:
 
Upvote 0
Glad we could help & thanks for the feedback.

The Filterxml function was provided by me, rather than Exceloki. ;)
 
Upvote 0
@Fluff
My mistake. Its early so....

I am having an error in some of the source data and formula.
Stand by for another post....
 
Upvote 0
@Fluff
I installed the Xl2bb add-in..... took a few minutes..

Error in row 1.
I have not yet put the IFERROR in the formulas..

SCADA_DATA.xlsb
ABCDEF
110.10.1.10:asi.OPC.1:Analog Item '333-10.ActualInterval<C1>'#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
210.10.1.10:asi.OPC.1:Analog Item '332-10.7113'10.10.1.10asi.OPC.1Analog Item'332-10.7113'#VALUE!
Sheet3
Cell Formulas
RangeFormula
B1:F2B1=FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1," '",":'"),"' ","':"),":","</m><m>")&"</m></k>","//m["&COLUMNS($B1:B1)&"]")



Here is a small excerpt from the sheet for explanation.
Some of the data is split incorrectly. Column F, some of the data 100}' should be in column E.
I have over 40,000 rows of data like this.

SCADA_DATA.xlsb
ABCDEF
110.10.1.10:asi.OPC.1:Analog Item '333-10.5106'10.10.1.10asi.OPC.1Analog Item'333-10.5106'#VALUE!
210.10.1.10:asi.OPC.1:Analog Item '333-10.5113'10.10.1.10asi.OPC.1Analog Item'333-10.5113'#VALUE!
310.10.1.10:asi.OPC.1:Analog Item '333-10.7102'10.10.1.10asi.OPC.1Analog Item'333-10.7102'#VALUE!
410.10.1.10:asi.OPC.1:Analog Item '333-10.7105'10.10.1.10asi.OPC.1Analog Item'333-10.7105'#VALUE!
510.10.1.10:asi.OPC.1:Analog Item '333-10.7106'10.10.1.10asi.OPC.1Analog Item'333-10.7106'#VALUE!
610.10.1.10:asi.OPC.1:Analog Item '333-10.7113'10.10.1.10asi.OPC.1Analog Item'333-10.7113'#VALUE!
710.10.1.10:asi.OPC.1:Analog Item '333-10.ActualInterval<C1>'#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
810.10.1.10:asi.OPC.1:Analog Item '333-10.Barometer{-1:100}'10.10.1.10asi.OPC.1Analog Item'333-10.Barometer{-1100}'
910.10.1.10:asi.OPC.1:Analog Item '333-10.LastPollLength'10.10.1.10asi.OPC.1Analog Item'333-10.LastPollLength'#VALUE!
1010.10.1.10:asi.OPC.1:Analog Item '333-10.MinutesSinceComplete'10.10.1.10asi.OPC.1Analog Item'333-10.MinutesSinceComplete'#VALUE!
1110.10.1.10:asi.OPC.1:Analog Item '333-10.TodayPctGoodPolls'10.10.1.10asi.OPC.1Analog Item'333-10.TodayPctGoodPolls'#VALUE!
1210.10.1.10:asi.OPC.1:Analog Item '333-11.5106'10.10.1.10asi.OPC.1Analog Item'333-11.5106'#VALUE!
1310.10.1.10:asi.OPC.1:Analog Item '333-11.5110'10.10.1.10asi.OPC.1Analog Item'333-11.5110'#VALUE!
1410.10.1.10:asi.OPC.1:Analog Item '333-11.5113'10.10.1.10asi.OPC.1Analog Item'333-11.5113'#VALUE!
1510.10.1.10:asi.OPC.1:Analog Item '333-11.5155'10.10.1.10asi.OPC.1Analog Item'333-11.5155'#VALUE!
1610.10.1.10:asi.OPC.1:Analog Item '333-11.7102'10.10.1.10asi.OPC.1Analog Item'333-11.7102'#VALUE!
1710.10.1.10:asi.OPC.1:Analog Item '333-11.7105'10.10.1.10asi.OPC.1Analog Item'333-11.7105'#VALUE!
1810.10.1.10:asi.OPC.1:Analog Item '333-11.7106'10.10.1.10asi.OPC.1Analog Item'333-11.7106'#VALUE!
1910.10.1.10:asi.OPC.1:Analog Item '333-11.7113'10.10.1.10asi.OPC.1Analog Item'333-11.7113'#VALUE!
2010.10.1.10:asi.OPC.1:Analog Item '333-11.ActualInterval<C1>'#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
2110.10.1.10:asi.OPC.1:Analog Item '333-11.Barometer{-1:100}'10.10.1.10asi.OPC.1Analog Item'333-11.Barometer{-1100}'
2210.10.1.10:asi.OPC.1:Analog Item '333-11.LastPollLength'10.10.1.10asi.OPC.1Analog Item'333-11.LastPollLength'#VALUE!
2310.10.1.10:asi.OPC.1:Analog Item '333-11.MinutesSinceComplete'10.10.1.10asi.OPC.1Analog Item'333-11.MinutesSinceComplete'#VALUE!
2410.10.1.10:asi.OPC.1:Analog Item '333-11.TodayPctGoodPolls'10.10.1.10asi.OPC.1Analog Item'333-11.TodayPctGoodPolls'#VALUE!
2510.10.1.10:asi.OPC.1:Analog Item '333-12.5106'10.10.1.10asi.OPC.1Analog Item'333-12.5106'#VALUE!
2610.10.1.10:asi.OPC.1:Analog Item '333-12.5113'10.10.1.10asi.OPC.1Analog Item'333-12.5113'#VALUE!
2710.10.1.10:asi.OPC.1:Analog Item '333-12.7102'10.10.1.10asi.OPC.1Analog Item'333-12.7102'#VALUE!
2810.10.1.10:asi.OPC.1:Analog Item '333-12.7105'10.10.1.10asi.OPC.1Analog Item'333-12.7105'#VALUE!
2910.10.1.10:asi.OPC.1:Analog Item '333-12.7106'10.10.1.10asi.OPC.1Analog Item'333-12.7106'#VALUE!
3010.10.1.10:asi.OPC.1:Analog Item '333-12.7113'10.10.1.10asi.OPC.1Analog Item'333-12.7113'#VALUE!
3110.10.1.10:asi.OPC.1:Analog Item '333-12.ActualInterval<C1>'#VALUE!#VALUE!#VALUE!#VALUE!#VALUE!
3210.10.1.10:asi.OPC.1:Analog Item '333-12.Barometer{-1:100}'10.10.1.10asi.OPC.1Analog Item'333-12.Barometer{-1100}'
3310.10.1.10:asi.OPC.1:Analog Item 'SS333.Barometer{-1:100}'10.10.1.10asi.OPC.1Analog Item'SS333.Barometer{-1100}'
3410.10.1.10:asi.OPC.1:Analog Item 'SS333.LastPollLength'10.10.1.10asi.OPC.1Analog Item'SS333.LastPollLength'#VALUE!
3510.10.1.10:asi.OPC.1:Analog Item 'SS333.MinutesSinceComplete'10.10.1.10asi.OPC.1Analog Item'SS333.MinutesSinceComplete'#VALUE!
3610.10.1.10:asi.OPC.1:Analog Item 'SS333.TodayPctGoodPolls'10.10.1.10asi.OPC.1Analog Item'SS333.TodayPctGoodPolls'#VALUE!
3710.10.1.10:asi.OPC.1:Digital Item '333-10.CommStatus' Bits 110.10.1.10asi.OPC.1Digital Item'333-10.CommStatus'Bits 1
3810.10.1.10:asi.OPC.1:Digital Item '333-10.ConnectionOverride' Bits 210.10.1.10asi.OPC.1Digital Item'333-10.ConnectionOverride'Bits 2
3910.10.1.10:asi.OPC.1:Digital Item '333-10.CurrentConnection' Bits 210.10.1.10asi.OPC.1Digital Item'333-10.CurrentConnection'Bits 2
4010.10.1.10:asi.OPC.1:Digital Item '333-10.Demand' Bits 110.10.1.10asi.OPC.1Digital Item'333-10.Demand'Bits 1
4110.10.1.10:asi.OPC.1:Digital Item '333-10.Enabled' Bits 110.10.1.10asi.OPC.1Digital Item'333-10.Enabled'Bits 1
4210.10.1.10:asi.OPC.1:Digital Item '333-10.Status' Bits 310.10.1.10asi.OPC.1Digital Item'333-10.Status'Bits 3
4310.10.1.10:asi.OPC.1:Digital Item '333-11.CommStatus' Bits 110.10.1.10asi.OPC.1Digital Item'333-11.CommStatus'Bits 1
4410.10.1.10:asi.OPC.1:Digital Item '333-11.ConnectionOverride' Bits 210.10.1.10asi.OPC.1Digital Item'333-11.ConnectionOverride'Bits 2
4510.10.1.10:asi.OPC.1:Digital Item '333-11.CurrentConnection' Bits 210.10.1.10asi.OPC.1Digital Item'333-11.CurrentConnection'Bits 2
4610.10.1.10:asi.OPC.1:Digital Item '333-11.Demand' Bits 110.10.1.10asi.OPC.1Digital Item'333-11.Demand'Bits 1
4710.10.1.10:asi.OPC.1:Digital Item '333-11.Enabled' Bits 110.10.1.10asi.OPC.1Digital Item'333-11.Enabled'Bits 1
4810.10.1.10:asi.OPC.1:Digital Item '333-11.Status' Bits 310.10.1.10asi.OPC.1Digital Item'333-11.Status'Bits 3
Sheet3
Cell Formulas
RangeFormula
B1:F48B1=FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1," '",":'"),"' ","':"),":","</m><m>")&"</m></k>","//m["&COLUMNS($B1:B1)&"]")
 
Last edited:
Upvote 0
That's because you have a < symbol in there. Try
Excel Formula: 
=FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"<","&lt;")," '",":'"),"' ","':"),":","</m><m>")&"</m></k>","//m["&COLUMNS($B1:B1)&"]")
 
Upvote 0
Fluff said:
That's because you have a < symbol in there. Try
Excel Formula: 
=FILTERXML("<k><m>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"<","&lt;")," '",":'"),"' ","':"),":","</m><m>")&"</m></k>","//m["&COLUMNS($B1:B1)&"]")
Click to expand...
I added a small excerpt to my last post for an example. The source data changes a bit.
Thanks for your time!
 
Upvote 0
You must log in or register to reply here.

Similar threads

PuntingJawa
  • Solved
Nested formula with multiple criteria
Replies
7
Views
124
PuntingJawa
PuntingJawa
J
  • Solved
Extract Data @ MID
Replies
8
Views
416
Peter_SSs
Peter_SSs
J
  • Question
Formula Assistance Please
Replies
5
Views
336
Peter_SSs
Peter_SSs
A
  • Solved
VBA to filter IP addresses based on first 2 octets
2
Replies
16
Views
1K
addistexan
A
SaraWitch
  • Solved
Comma needed in CONCAT formula
Replies
5
Views
119
SaraWitch
SaraWitch

Forum statistics

Threads
1,215,460
Messages
6,124,949
Members
449,198
Latest member
MhammadishaqKhan

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