Column Output 2

V

vmjan02

Well-known Member
Joined
Aug 15, 2012
Messages
1,134
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
Output 1 is all perfert I am able to get it but not sure how to get the result for Output 2 , I am not able to fix this part,

Huawei-INTMAC.csv
ABCDE
6PE-AggX16A-Abha-701-2----------------------------------------------------------------------------------------------------------------------Output 1Output 2
7PE-AggX16A-Abha-701-278da-6ea3-9c81 Vlan2427 - - Tun0/0/200 dynamic 1/31058  
8PE-AggX16A-Abha-701-20002-71e8-3eba 2027 - - Eth-Trunk4 dynamic 1/- 0002-71e8-3ebaEth-Trunk4
9PE-AggX16A-Abha-701-20002-71e7-9ea1 Vlan1031 3044 - Eth-Trunk6.1031 dynamic 1/- 0002-71e7-9ea1Eth-Trunk6.1031
10PE-AggX16A-Abha-701-20002-71ca-8477 Vlan1031 3041 - Eth-Trunk5.1031 dynamic 1/- 0002-71ca-8477Eth-Trunk5.1031
11PE-AggX16A-Abha-701-21845-9348-8ff1 Vlan1031 3044 - Eth-Trunk6.1031 dynamic 1/- 1845-9348-8ff1Eth-Trunk6.1031
12PE-AggX16A-Abha-701-2f479-60ed-0fa4 BD 3201 - - Tun0/0/200 dynamic 1/32052  
13PE-AggX16A-Abha-701-20002-71f3-a0ab Vlan1031 3042 - Eth-Trunk4.1031 dynamic 1/- 0002-71f3-a0abEth-Trunk4.1031
14PE-AggX16A-Abha-701-2b426-5d8e-2e91 Vlan1031 3041 - Eth-Trunk63.3041 dynamic 1/- b426-5d8e-2e91Eth-Trunk63.3041
15PE-AggX16A-Abha-701-20002-71f4-2875 Vlan1031 3044 - Eth-Trunk6.1031 dynamic 1/- 0002-71f4-2875Eth-Trunk6.1031
16PE-AggX16A-Abha-701-20002-71fb-99d7 Vlan1031 3042 - Eth-Trunk4.1031 dynamic 1/- 0002-71fb-99d7Eth-Trunk4.1031
17PE-AggX16A-Abha-701-21845-935c-f421 Vlan1031 3044 - Eth-Trunk6.1031 dynamic 1/- 1845-935c-f421Eth-Trunk6.1031
18PE-AggX16A-Abha-701-2803e-48b8-8e12 2029 - - Eth-Trunk6 dynamic 1/- 803e-48b8-8e12Eth-Trunk6
19PE-AggX16A-Abha-701-20002-71fb-a04d Vlan1031 3041 - Eth-Trunk63.3041 dynamic 1/- 0002-71fb-a04dEth-Trunk63.3041
20PE-AggX16A-Abha-701-20002-71fb-92da 2027 - - Eth-Trunk4 dynamic 1/- 0002-71fb-92daEth-Trunk4
21PE-AggX16A-Abha-701-2f08e-db22-0883 3689 - - Eth-Trunk6 dynamic 1/- f08e-db22-0883Eth-Trunk6
Huawei-INTMAC
Cell Formulas
RangeFormula
D7:D21D7=IF(ISERROR(FIND(" Eth",B7)),"",TEXTBEFORE(B7," "))
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Sort by date Sort by votes
Try This

=IFERROR(TEXTBEFORE(MID(B7,SEARCH("Eth",B7),LEN(B7)),"dynamic"),"")
Click to expand...


1701929610116.png




*If there’s any unclear information please let me know
 
Upvote 0
@SunnyAlv
That formula will also return all the space characters between the "Eth-" term and the word "dynamic". I think more likely the OP would want this

Excel Formula: 
=IFERROR(TRIM(TEXTBEFORE(MID(B7,SEARCH("Eth",B7),LEN(B7)),"dynamic")),"")

BTW, it would also be better if you used the 'formula' code tags
1701932230716.png
rather than 'quote' tags when posting a formula.

@vmjan02
Another option could be this.

23 12 07.xlsm
BCE
6----------------------------------------------------------------------------------------------------------------------Output 2
778da-6ea3-9c81 Vlan2427 - - Tun0/0/200 dynamic 1/31058  
80002-71e8-3eba 2027 - - Eth-Trunk4 dynamic 1/- Eth-Trunk4
90002-71e7-9ea1 Vlan1031 3044 - Eth-Trunk6.1031 dynamic 1/- Eth-Trunk6.1031
100002-71ca-8477 Vlan1031 3041 - Eth-Trunk5.1031 dynamic 1/- Eth-Trunk5.1031
111845-9348-8ff1 Vlan1031 3044 - Eth-Trunk6.1031 dynamic 1/- Eth-Trunk6.1031
12f479-60ed-0fa4 BD 3201 - - Tun0/0/200 dynamic 1/32052  
130002-71f3-a0ab Vlan1031 3042 - Eth-Trunk4.1031 dynamic 1/- Eth-Trunk4.1031
14b426-5d8e-2e91 Vlan1031 3041 - Eth-Trunk63.3041 dynamic 1/- Eth-Trunk63.3041
150002-71f4-2875 Vlan1031 3044 - Eth-Trunk6.1031 dynamic 1/- Eth-Trunk6.1031
160002-71fb-99d7 Vlan1031 3042 - Eth-Trunk4.1031 dynamic 1/- Eth-Trunk4.1031
171845-935c-f421 Vlan1031 3044 - Eth-Trunk6.1031 dynamic 1/- Eth-Trunk6.1031
18803e-48b8-8e12 2029 - - Eth-Trunk6 dynamic 1/- Eth-Trunk6
190002-71fb-a04d Vlan1031 3041 - Eth-Trunk63.3041 dynamic 1/- Eth-Trunk63.3041
200002-71fb-92da 2027 - - Eth-Trunk4 dynamic 1/- Eth-Trunk4
21f08e-db22-0883 3689 - - Eth-Trunk6 dynamic 1/- Eth-Trunk6
Sheet2 (2)
Cell Formulas
RangeFormula
E7:E21E7=LET(ts,TEXTSPLIT(B7," "),FILTER(ts,ISNUMBER(SEARCH("Eth-",ts)),""))
 
Upvote 0
In case you want an alternative. If you change the font to Courier New (a monospaced font ie not proportional), you will find that the data format is actually fixed width.
This means you could use Data > Text to Columns > Fixed width, to split it.
It also mean you could work out the start and end position once and then apply that consistently using just left and mid

20231207 Split Fixed Width Text vmjan02.xlsx
ABCDE
6PE-AggX16A-Abha-701-2----------------------------------------------------------------------------------------------------------------------Output 1Output 2
7PE-AggX16A-Abha-701-278da-6ea3-9c81 Vlan2427 - - Tun0/0/200 dynamic 1/31058   
8PE-AggX16A-Abha-701-20002-71e8-3eba 2027 - - Eth-Trunk4 dynamic 1/- 0002-71e8-3ebaEth-Trunk4
9PE-AggX16A-Abha-701-20002-71e7-9ea1 Vlan1031 3044 - Eth-Trunk6.1031 dynamic 1/- 0002-71e7-9ea1Eth-Trunk6.1031
10PE-AggX16A-Abha-701-20002-71ca-8477 Vlan1031 3041 - Eth-Trunk5.1031 dynamic 1/- 0002-71ca-8477Eth-Trunk5.1031
11PE-AggX16A-Abha-701-21845-9348-8ff1 Vlan1031 3044 - Eth-Trunk6.1031 dynamic 1/- 1845-9348-8ff1Eth-Trunk6.1031
12PE-AggX16A-Abha-701-2f479-60ed-0fa4 BD 3201 - - Tun0/0/200 dynamic 1/32052   
13PE-AggX16A-Abha-701-20002-71f3-a0ab Vlan1031 3042 - Eth-Trunk4.1031 dynamic 1/- 0002-71f3-a0abEth-Trunk4.1031
14PE-AggX16A-Abha-701-2b426-5d8e-2e91 Vlan1031 3041 - Eth-Trunk63.3041 dynamic 1/- b426-5d8e-2e91Eth-Trunk63.3041
15PE-AggX16A-Abha-701-20002-71f4-2875 Vlan1031 3044 - Eth-Trunk6.1031 dynamic 1/- 0002-71f4-2875Eth-Trunk6.1031
16PE-AggX16A-Abha-701-20002-71fb-99d7 Vlan1031 3042 - Eth-Trunk4.1031 dynamic 1/- 0002-71fb-99d7Eth-Trunk4.1031
17PE-AggX16A-Abha-701-21845-935c-f421 Vlan1031 3044 - Eth-Trunk6.1031 dynamic 1/- 1845-935c-f421Eth-Trunk6.1031
18PE-AggX16A-Abha-701-2803e-48b8-8e12 2029 - - Eth-Trunk6 dynamic 1/- 803e-48b8-8e12Eth-Trunk6
19PE-AggX16A-Abha-701-20002-71fb-a04d Vlan1031 3041 - Eth-Trunk63.3041 dynamic 1/- 0002-71fb-a04dEth-Trunk63.3041
20PE-AggX16A-Abha-701-20002-71fb-92da 2027 - - Eth-Trunk4 dynamic 1/- 0002-71fb-92daEth-Trunk4
21PE-AggX16A-Abha-701-2f08e-db22-0883 3689 - - Eth-Trunk6 dynamic 1/- f08e-db22-0883Eth-Trunk6
Sheet1
Cell Formulas
RangeFormula
D7:D21D7=IF(E7="","",LEFT(B7,14))
E7:E21E7=LET(sTxt,TRIM(MID(B7,62,97-62)), IF(LEFT(sTxt,3)="Eth",sTxt,""))
 
Upvote 0
Thank you every one of you, its a great help as all the results are perfect, I hope the functions will work in 2016 office as well.
 
Upvote 0
As it stands all the solutions use MS 365 functions.
I don't really need the LET, this will work in 2016

20231207 Split Fixed Width Text vmjan02.xlsx
BCDE
6----------------------------------------------------------------------------------------------------------------------Output 1Output 2
778da-6ea3-9c81 Vlan2427 - - Tun0/0/200 dynamic 1/31058   
80002-71e8-3eba 2027 - - Eth-Trunk4 dynamic 1/- 0002-71e8-3ebaEth-Trunk4
90002-71e7-9ea1 Vlan1031 3044 - Eth-Trunk6.1031 dynamic 1/- 0002-71e7-9ea1Eth-Trunk6.1031
100002-71ca-8477 Vlan1031 3041 - Eth-Trunk5.1031 dynamic 1/- 0002-71ca-8477Eth-Trunk5.1031
111845-9348-8ff1 Vlan1031 3044 - Eth-Trunk6.1031 dynamic 1/- 1845-9348-8ff1Eth-Trunk6.1031
Sheet1 2016
Cell Formulas
RangeFormula
D7:D11D7=IF(E7="","",LEFT(B7,14))
E7:E11E7=IF(LEFT(TRIM(MID(B7,62,97-62)),3)="Eth",TRIM(MID(B7,62,97-62)),"")
 
Upvote 0
Alex Blakenburg said:
This means you could use Data > Text to Columns > Fixed width, to split it.
Click to expand...
Except that wouldn't produce the expected results shown.

@vmjan02
In future please state in post 1 if versions other than that shown in your profile are required. That is especially so here where you say you have Output 1 covered yet for that results you have used a function that is not available in Excel 2016. ;)

If 2016 is required for Output 2 then using Alex's pick-up on the regular character spacings, here is a shorter way to get those expected results.

23 12 07.xlsm
BCE
6----------------------------------------------------------------------------------------------------------------------Output 2
778da-6ea3-9c81 Vlan2427 - - Tun0/0/200 dynamic 1/31058  
80002-71e8-3eba 2027 - - Eth-Trunk4 dynamic 1/- Eth-Trunk4
90002-71e7-9ea1 Vlan1031 3044 - Eth-Trunk6.1031 dynamic 1/- Eth-Trunk6.1031
100002-71ca-8477 Vlan1031 3041 - Eth-Trunk5.1031 dynamic 1/- Eth-Trunk5.1031
111845-9348-8ff1 Vlan1031 3044 - Eth-Trunk6.1031 dynamic 1/- Eth-Trunk6.1031
12f479-60ed-0fa4 BD 3201 - - Tun0/0/200 dynamic 1/32052  
130002-71f3-a0ab Vlan1031 3042 - Eth-Trunk4.1031 dynamic 1/- Eth-Trunk4.1031
14b426-5d8e-2e91 Vlan1031 3041 - Eth-Trunk63.3041 dynamic 1/- Eth-Trunk63.3041
150002-71f4-2875 Vlan1031 3044 - Eth-Trunk6.1031 dynamic 1/- Eth-Trunk6.1031
160002-71fb-99d7 Vlan1031 3042 - Eth-Trunk4.1031 dynamic 1/- Eth-Trunk4.1031
171845-935c-f421 Vlan1031 3044 - Eth-Trunk6.1031 dynamic 1/- Eth-Trunk6.1031
18803e-48b8-8e12 2029 - - Eth-Trunk6 dynamic 1/- Eth-Trunk6
190002-71fb-a04d Vlan1031 3041 - Eth-Trunk63.3041 dynamic 1/- Eth-Trunk63.3041
200002-71fb-92da 2027 - - Eth-Trunk4 dynamic 1/- Eth-Trunk4
21f08e-db22-0883 3689 - - Eth-Trunk6 dynamic 1/- Eth-Trunk6
Eth
Cell Formulas
RangeFormula
E7:E21E7=TRIM(MID(B7,62,35*(MID(B7,62,3)="Eth")))
 
Upvote 0
Sure will keep that i mind thanks every one for the great help you all are gem and its great to know thanks once again.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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