How to get a text string from a single cell in excel

A

AFZAL SOHAIL

Board Regular
Joined
May 31, 2023
Messages
115
Office Version
  1. 2021
  2. 2016
Platform
  1. Windows
Hello Dear Team:-
I need your help, I have text data in col-a which is text string, I want to get the certain text string from the data is as under:-
By a single or more than single formula.
Thanks a lot in advance.

text stringexpected results
DSLAM Down on ctr-119_156_217_13-ma5616; SNMP;CTR.OKA.Chak42GB.DSLAM.01Chak42GB
DSLAM Down on ctr-119_156_217_14-ma5600; SNMP;CTR.OKA.Chak47.DSLAM.01Chak47
DSLAM Down on ctr-119_156_219_133-ma5615; SNMP;CTR.SWL.Chak1409L.DSLAM.01Chak1409L
DSLAM Down on ctr-119_156_219_132-ma5616; SNMP;CTR.KSR.AtheelPur.DSLAM.01AtheelPur
DSLAM Down on ltn-119_156_202_51-ma5616; SNMP;LTN.LHR.Barki.DSLAM.01Barki
DSLAM Down on ltn-119_156_202_7-ma5615; SNMP;LTN.LHR.Minhala.DSLAM.01Minhala
DSLAM Down on ltn-119_156_202_17-ma5616; SNMP;LTN.LHR.Barki.DSLAM.01Barki
DSLAM Down on ltn-119_156_202_6-ma5615; SNMP;LTN.LHR.LhrPadhana.DSLAM.01LhrPadhana
DSLAM Down on ctr-119_156_219_133-ma5615; SNMP;CTR.SWL.Chak1409L.DSLAM.01Chak1409L
DSLAM Down on ctr-119_156_219_133-ma5615; SNMP;CTR.SWL.Chak1409L.DSLAM.01Chak1409L
DSLAM Down on ctr-119_156_219_133-ma5615; SNMP;CTR.SWL.Chak1409L.DSLAM.01Chak1409L
DSLAM Down on ltn-119_156_202_7-ma5615; SNMP;LTN.LHR.Minhala.DSLAM.01Minhala
DSLAM Down on ltn-119_156_202_51-ma5616; SNMP;LTN.LHR.Barki.DSLAM.01Barki
DSLAM Down on ltn-119_156_202_17-ma5616; SNMP;LTN.LHR.Barki.DSLAM.01Barki
DSLAM Down on ltn-119_156_202_6-ma5615; SNMP;LTN.LHR.LhrPadhana.DSLAM.01LhrPadhana
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
How about
Fluff.xlsm
AB
1
2DSLAM Down on ctr-119_156_217_13-ma5616; SNMP;CTR.OKA.Chak42GB.DSLAM.01Chak42GB
3DSLAM Down on ctr-119_156_217_14-ma5600; SNMP;CTR.OKA.Chak47.DSLAM.01Chak47
4DSLAM Down on ctr-119_156_219_133-ma5615; SNMP;CTR.SWL.Chak1409L.DSLAM.01Chak1409L
5DSLAM Down on ctr-119_156_219_132-ma5616; SNMP;CTR.KSR.AtheelPur.DSLAM.01AtheelPur
6DSLAM Down on ltn-119_156_202_51-ma5616; SNMP;LTN.LHR.Barki.DSLAM.01Barki
7DSLAM Down on ltn-119_156_202_7-ma5615; SNMP;LTN.LHR.Minhala.DSLAM.01Minhala
8DSLAM Down on ltn-119_156_202_17-ma5616; SNMP;LTN.LHR.Barki.DSLAM.01Barki
9DSLAM Down on ltn-119_156_202_6-ma5615; SNMP;LTN.LHR.LhrPadhana.DSLAM.01LhrPadhana
10DSLAM Down on ctr-119_156_219_133-ma5615; SNMP;CTR.SWL.Chak1409L.DSLAM.01Chak1409L
11DSLAM Down on ctr-119_156_219_133-ma5615; SNMP;CTR.SWL.Chak1409L.DSLAM.01Chak1409L
12DSLAM Down on ctr-119_156_219_133-ma5615; SNMP;CTR.SWL.Chak1409L.DSLAM.01Chak1409L
13DSLAM Down on ltn-119_156_202_7-ma5615; SNMP;LTN.LHR.Minhala.DSLAM.01Minhala
14DSLAM Down on ltn-119_156_202_51-ma5616; SNMP;LTN.LHR.Barki.DSLAM.01Barki
15DSLAM Down on ltn-119_156_202_17-ma5616; SNMP;LTN.LHR.Barki.DSLAM.01Barki
16DSLAM Down on ltn-119_156_202_6-ma5615; SNMP;LTN.LHR.LhrPadhana.DSLAM.01LhrPadhana
Master
Cell Formulas
RangeFormula
B2:B16B2=REPLACE(LEFT(A2,FIND("^",SUBSTITUTE(A2,".","^",3))-1),1,FIND("^",SUBSTITUTE(A2,".","^",2)),"")
 
Upvote 0
A couple of other options depending on which versions it needs to work in.

23 07 10.xlsm
ABC
1text string
2DSLAM Down on ctr-119_156_217_13-ma5616; SNMP;CTR.OKA.Chak42GB.DSLAM.01Chak42GBChak42GB
3DSLAM Down on ctr-119_156_217_14-ma5600; SNMP;CTR.OKA.Chak47.DSLAM.01Chak47Chak47
4DSLAM Down on ctr-119_156_219_133-ma5615; SNMP;CTR.SWL.Chak1409L.DSLAM.01Chak1409LChak1409L
5DSLAM Down on ctr-119_156_219_132-ma5616; SNMP;CTR.KSR.AtheelPur.DSLAM.01AtheelPurAtheelPur
6DSLAM Down on ltn-119_156_202_51-ma5616; SNMP;LTN.LHR.Barki.DSLAM.01BarkiBarki
7DSLAM Down on ltn-119_156_202_7-ma5615; SNMP;LTN.LHR.Minhala.DSLAM.01MinhalaMinhala
8DSLAM Down on ltn-119_156_202_17-ma5616; SNMP;LTN.LHR.Barki.DSLAM.01BarkiBarki
9DSLAM Down on ltn-119_156_202_6-ma5615; SNMP;LTN.LHR.LhrPadhana.DSLAM.01LhrPadhanaLhrPadhana
10DSLAM Down on ctr-119_156_219_133-ma5615; SNMP;CTR.SWL.Chak1409L.DSLAM.01Chak1409LChak1409L
11DSLAM Down on ctr-119_156_219_133-ma5615; SNMP;CTR.SWL.Chak1409L.DSLAM.01Chak1409LChak1409L
12DSLAM Down on ctr-119_156_219_133-ma5615; SNMP;CTR.SWL.Chak1409L.DSLAM.01Chak1409LChak1409L
13DSLAM Down on ltn-119_156_202_7-ma5615; SNMP;LTN.LHR.Minhala.DSLAM.01MinhalaMinhala
14DSLAM Down on ltn-119_156_202_51-ma5616; SNMP;LTN.LHR.Barki.DSLAM.01BarkiBarki
15DSLAM Down on ltn-119_156_202_17-ma5616; SNMP;LTN.LHR.Barki.DSLAM.01BarkiBarki
16DSLAM Down on ltn-119_156_202_6-ma5615; SNMP;LTN.LHR.LhrPadhana.DSLAM.01LhrPadhanaLhrPadhana
Extract Text
Cell Formulas
RangeFormula
B2:B16B2=TAKE(TAKE(TEXTSPLIT(A2,"."),,-3),,1)
C2:C16C2=TRIM(LEFT(RIGHT(SUBSTITUTE(A2,".",REPT(" ",100)),300),100))


Edit: Or even
Excel Formula: 
=CHOOSECOLS(TEXTSPLIT(A2,"."),3)
 
Upvote 0
FILTERXML should also work:
Book1
AB
1text string
2DSLAM Down on ctr-119_156_217_13-ma5616; SNMP;CTR.OKA.Chak42GB.DSLAM.01Chak42GB
3DSLAM Down on ctr-119_156_217_14-ma5600; SNMP;CTR.OKA.Chak47.DSLAM.01Chak47
4DSLAM Down on ctr-119_156_219_133-ma5615; SNMP;CTR.SWL.Chak1409L.DSLAM.01Chak1409L
5DSLAM Down on ctr-119_156_219_132-ma5616; SNMP;CTR.KSR.AtheelPur.DSLAM.01AtheelPur
6DSLAM Down on ltn-119_156_202_51-ma5616; SNMP;LTN.LHR.Barki.DSLAM.01Barki
7DSLAM Down on ltn-119_156_202_7-ma5615; SNMP;LTN.LHR.Minhala.DSLAM.01Minhala
8DSLAM Down on ltn-119_156_202_17-ma5616; SNMP;LTN.LHR.Barki.DSLAM.01Barki
9DSLAM Down on ltn-119_156_202_6-ma5615; SNMP;LTN.LHR.LhrPadhana.DSLAM.01LhrPadhana
10DSLAM Down on ctr-119_156_219_133-ma5615; SNMP;CTR.SWL.Chak1409L.DSLAM.01Chak1409L
11DSLAM Down on ctr-119_156_219_133-ma5615; SNMP;CTR.SWL.Chak1409L.DSLAM.01Chak1409L
12DSLAM Down on ctr-119_156_219_133-ma5615; SNMP;CTR.SWL.Chak1409L.DSLAM.01Chak1409L
13DSLAM Down on ltn-119_156_202_7-ma5615; SNMP;LTN.LHR.Minhala.DSLAM.01Minhala
14DSLAM Down on ltn-119_156_202_51-ma5616; SNMP;LTN.LHR.Barki.DSLAM.01Barki
15DSLAM Down on ltn-119_156_202_17-ma5616; SNMP;LTN.LHR.Barki.DSLAM.01Barki
16DSLAM Down on ltn-119_156_202_6-ma5615; SNMP;LTN.LHR.LhrPadhana.DSLAM.01LhrPadhana
Sheet1
Cell Formulas
RangeFormula
B2:B16B2=FILTERXML("<t><s>" & SUBSTITUTE(A2,".","</s><s>") & "</s></t>","//s[3]")
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,215,444
Messages
6,124,892
Members
449,194
Latest member
JayEggleton

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