Getting out numbers

V

vmjan02

Well-known Member
Joined
Aug 15, 2012
Messages
1,132
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
I have this table and it takes around 5 hrs to complete the task, not sure as wh to get the out put. tried multipal ways but no luck, any formula to fix it.

DataResult in Text
CoreNCS8-MRSTRDEF-1 Hu0/4/0/25 Link Monitoring PRN 116933***0/4/0/25
MetroCore-PTX5K-Abar-422-1 et-7/1/7**7/1/7
MetroCore-PTX5K-Bur-601-1 et-7/1/9** //High-Latancy Link offloaded in upload direction7/1/9
PE-AggX16A-Abha-701-2 Gi3/0/11 **3/0/11
PE-AggX16A-Kham-702-1 Gi16/1/10 **16/1/10
PE-AggX16A-Abha-701-2 Gi4/1/10 **4/1/10
PE-AggX16A-Kham-702-1 Gi3/0/11 **3/0/11
PE-Agg22-Hut-111-5 Te 0/1/0/11 **0/1/0/11
PE-AggX16-Bil-711-1 Gi1/0/6 ** METRIC-RAISED IN-UPLOAD-DIRECTION1/0/6
PE-AggX16-Bil-711-1 GigabitEthernet1/0/4**1/0/4
PE-AggX16-Bil-711-1 GigabitEthernet2/0/3**2/0/3
PE-AggX16-Bil-711-1 Gi1/0/10 **1/0/10
PE-AggX16-Bil-711-1 Gi1/0/11 **1/0/11
CoreNCS-Baha-712-1 TenG 0/6/0/8**0/6/0/8
CoreNCS-Baha-712-1 TenG 0/6/0/24**0/6/0/24
CoreNCS-Abha-701-1 Te0/6/0/45**0/6/0/45
CoreNCS-Abha-701-1 TenG 0/6/0/48**0/6/0/48
PE-Agg22-Mar-231-5 Interface Te0/0/1/9**0/0/1/9
 
Sort by date Sort by votes
Peter_SSs said:
Can you confirm what the correct expected result is for this line in your post #4 sample data?

View attachment 101888
Click to expand...
the code is giving me blank result but muanl formula is perfect, not sure why.

VBA Code: 
Range("G1").Value = "IfAlias Remort Interface"
With Worksheets("Working Sheet")
    With .Range("G2:G" & .Range("A" & .Rows.Count).End(xlUp).Row)
        .Formula = "=LET(c,MID(B2,SEQUENCE(LEN(B2)),1),s,CONCAT(IF(ISNUMBER(-c),c,IF(c="/",c,REPT(" ",100)))),TRIM(MID(s,FIND("/",s)-2,100)))"
        '.Value = .Value
     End With
End With
 
Upvote 0

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
vmjan02 said:
the code is giving me blank result
Click to expand...
If you are getting a blank result then that code in post #11 is not the code you are using. That code is not valid code and will give a 'Compile error - Syntax error'

Also, ...
  1. You did not directly address my question in post #9. Could you please do so?

  2. You have indicated that post #8 formula returns the correct results and indicated that the post #3 formulas also do apart from referencing the wrong column. Yet all of those formulas return a result different to your shown expected results in post #1 for the row below.
1699999578443.png


Please explain clearly, in words, exactly what should be extracted from the text.
 
Upvote 0
@Peter_SSs
Sorry as i missed your qurier, once again I am sorry for the mis understanding and confusion. :unsure:

I am sure I have cleared the qurier below, as the best I can. :)

#1 my post the sample data was not correctly received on the #4 post have updated the correct samplete data.

the expected result should be 0/0/022
1700028241134.png


as per the table below, Actual reuslt or output is in column D, and in column C the result is not as required.


Book1
BCD
1Actual DataEg 1Acutal Result
2***connected to CoreNCS8-MRSTRDEF-1 Hu0/4/0/25 Link Monitoring PRN 116933***0/4/0/25 0/4/0/25
3**Connected to MetroCore-PTX5K-Abar-422-1 et-7/1/7**7/1/77/1/7
4**Connected to MetroCore-PTX5K-Bur-601-1 et-7/1/9** //High-Latancy_Link_offloaded_in_upload_direction7/1/97/1/9
5** Connected to PE-AggX16A-Abha-701-2 Gi3/0/11 **3/0/11 3/0/11
6** Connected to PE-AggX16A-Kham-702-1 Gi16/1/10 **6/1/10 6/1/10
7** Connected to PE-AggX16A-Abha-701-2 Gi4/1/10 **4/1/10 4/1/10
8** Connected to PE-AggX16A-Kham-702-1 Gi3/0/11 **3/0/11 3/0/11
9** Connected to PE-Agg22-Hut-111-5 Te 0/1/0/11 **0/1/0/11 0/1/0/11
10** Connected to PE-AggX16-Bil-711-1 Gi1/0/6 **_METRIC-RAISED_IN-UPLOAD-DIRECTION1/0/6 _1/0/6
11**connected to PE-AggX16-Bil-711-1 GigabitEthernet1/0/4**1/0/41/0/4
12**connected to PE-AggX16-Bil-711-1 GigabitEthernet2/0/3**2/0/32/0/3
13** Connected to PE-AggX16-Bil-711-1 Gi1/0/10 **1/0/10 1/0/10
14** Connected to PE-AggX16-Bil-711-1 Gi1/0/11 **1/0/11 1/0/11
15**Connected to CoreNCS-Baha-712-1 TenG 0/6/0/8**0/6/0/80/6/0/8
16**Connected to CoreNCS-Baha-712-1 TenG 0/6/0/24**0/6/0/240/6/0/24
17**Connected to CoreNCS-Abha-701-1 Te0/6/0/45**0/6/0/450/6/0/45
18**Connected to CoreNCS-Abha-701-1 TenG 0/6/0/48**0/6/0/480/6/0/48
19**UPE-UPLINK-Connected to PE-Agg22-Mar-231-5 Interface Te0/0/1/9**0/0/1/90/0/1/9
20**connected to PE-Agg22-Mar-231-5 Interface Te0/1/1/7**0/1/1/70/1/1/7
21** Connected to PE-AggX16A-Kham-702-1 interface GigabitEthernet3/1/4 **3/1/4 3/1/4
22** Connected to PE-Agg22-Aziz-223-6 Te0/3/0/17**0/3/0/170/3/0/17
23** Connected to PE-Agg22-Aziz-223-6 Te0/3/0/9** **0/3/0/90/3/0/9
24"Connected to UPE3-AIRPEAAC-1 Migreated to PRE-AGG9K-387-00-000-1 Gi100/0/0/22"0/0/0/22"0/0/0/22
25** Connected to PE-AggX16-Muha-751-1 Gi1/8/0/4 **METRIC_OFFLOAD_UPLOAD_SEP_2022*1/8/0/4 1/8/0/4
26** Connected to PE-AggX16A-Abha-701-2 Ge4/1/6 **_METRIC-RAISED_IN-UPLOAD-DIRECTION4/1/6 _4/1/6
27** Connected to PE-AggX16A-Abha-701-2 Gi4/0/2 **_METRIC-RAISED_IN-UPLOAD-DIRECTION4/0/2 _4/0/2
28** Connected to PE-Agg22-Mar-231-5 interface TenGigabitEthernet0/2/0/4 **0/2/0/4 0/2/0/4
29** Connected to PE-Agg22-Waj-232-3 Te0/1/0/15 **0/1/0/15 0/1/0/15
30** Connected to PE-AggX16-Bis-709-1 Gi1/0/7** METRIC_OFFLOAD_UPLOAD_SEP_20221/0/7 M1/0/7
Sheet1
Cell Formulas
RangeFormula
C2:C30C2=SUBSTITUTE(SUBSTITUTE(LEFT(REPLACE(B2,1,FIND("/",B2,FIND("/",B2))-2,""),9),"*","")," /","")
D2:D30D2=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE(B2,1,FIND("/",B2,FIND("/",B2))-2,""),"""",""),"*"," ")," ",REPT(" ",100)),100))



In my #10 the #8 result is not working as no error but blank result

VBA Code: 
Range("G1").Value = "IfAlias Remort Interface"
With Worksheets("Working Sheet")
With .Range("G2:G" & .Range("A" & .Rows.Count).End(xlUp).Row)

.Formula = "=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE(B2,1,FIND(""/"",B2,FIND(""/"",B2))-2,""""),"""""",""""),""*"","" ""),"" "",REPT("" "",100)),100))"
'.Value = .Value
End With
End With
 
Upvote 0
vmjan02 said:
the expected result should be 0/0/022
Click to expand...
:confused: Really? Why?
Are you sure it shouldn't be this? 0/0/0/22


That vba code still does not run for me:

1700028961937.png


Could you post the whole code if my suggestion below does not work for you.?

Also, What sheet is this supposed to be happening on?
vmjan02 said:
Range("G1").Value = "IfAlias Remort Interface"
Click to expand...

VBA Code: 
With Worksheets("Working Sheet")
  .Range("G1").Value = "IfAlias Remort Interface"
  With .Range("G2:G" & .Range("A" & .Rows.Count).End(xlUp).Row)
    .Formula2 = "=LET(c,MID(B2,SEQUENCE(LEN(B2)),1),s,CONCAT(IF(ISNUMBER(-c),c,IF(c=""/"",c,REPT("" "",100)))),TRIM(MID(s,FIND(""/"",s)-1,100)))"
'    .Value = .Value
  End With
End With

These are my results

vmjan02.xlsm
BG
1Actual DataIfAlias Remort Interface
2***connected to CoreNCS8-MRSTRDEF-1 Hu0/4/0/25 Link Monitoring PRN 116933***0/4/0/25
3**Connected to MetroCore-PTX5K-Abar-422-1 et-7/1/7**7/1/7
4**Connected to MetroCore-PTX5K-Bur-601-1 et-7/1/9** //High-Latancy_Link_offloaded_in_upload_direction7/1/9
5** Connected to PE-AggX16A-Abha-701-2 Gi3/0/11 **3/0/11
6** Connected to PE-AggX16A-Kham-702-1 Gi16/1/10 **6/1/10
7** Connected to PE-AggX16A-Abha-701-2 Gi4/1/10 **4/1/10
8** Connected to PE-AggX16A-Kham-702-1 Gi3/0/11 **3/0/11
9** Connected to PE-Agg22-Hut-111-5 Te 0/1/0/11 **0/1/0/11
10** Connected to PE-AggX16-Bil-711-1 Gi1/0/6 **_METRIC-RAISED_IN-UPLOAD-DIRECTION1/0/6
11**connected to PE-AggX16-Bil-711-1 GigabitEthernet1/0/4**1/0/4
12**connected to PE-AggX16-Bil-711-1 GigabitEthernet2/0/3**2/0/3
13** Connected to PE-AggX16-Bil-711-1 Gi1/0/10 **1/0/10
14** Connected to PE-AggX16-Bil-711-1 Gi1/0/11 **1/0/11
15**Connected to CoreNCS-Baha-712-1 TenG 0/6/0/8**0/6/0/8
16**Connected to CoreNCS-Baha-712-1 TenG 0/6/0/24**0/6/0/24
17**Connected to CoreNCS-Abha-701-1 Te0/6/0/45**0/6/0/45
18**Connected to CoreNCS-Abha-701-1 TenG 0/6/0/48**0/6/0/48
19**UPE-UPLINK-Connected to PE-Agg22-Mar-231-5 Interface Te0/0/1/9**0/0/1/9
20**connected to PE-Agg22-Mar-231-5 Interface Te0/1/1/7**0/1/1/7
21** Connected to PE-AggX16A-Kham-702-1 interface GigabitEthernet3/1/4 **3/1/4
22** Connected to PE-Agg22-Aziz-223-6 Te0/3/0/17**0/3/0/17
23** Connected to PE-Agg22-Aziz-223-6 Te0/3/0/9** **0/3/0/9
24"Connected to UPE3-AIRPEAAC-1 Migreated to PRE-AGG9K-387-00-000-1 Gi100/0/0/22"0/0/0/22
25** Connected to PE-AggX16-Muha-751-1 Gi1/8/0/4 **METRIC_OFFLOAD_UPLOAD_SEP_2022*1/8/0/4
26** Connected to PE-AggX16A-Abha-701-2 Ge4/1/6 **_METRIC-RAISED_IN-UPLOAD-DIRECTION4/1/6
27** Connected to PE-AggX16A-Abha-701-2 Gi4/0/2 **_METRIC-RAISED_IN-UPLOAD-DIRECTION4/0/2
28** Connected to PE-Agg22-Mar-231-5 interface TenGigabitEthernet0/2/0/4 **0/2/0/4
29** Connected to PE-Agg22-Waj-232-3 Te0/1/0/15 **0/1/0/15
30** Connected to PE-AggX16-Bis-709-1 Gi1/0/7** METRIC_OFFLOAD_UPLOAD_SEP_20221/0/7
Working Sheet
 
Upvote 0
@Peter_SSs

Yes result should be 100/0/0/22, you are right

Here is the code and work sheet is correct "Working Sheet", here it is giving me blank result not sure is it because of office 365 or .formula
VBA Code: 
Range("G1").Value = "IfAlias Remort Interface"
With Worksheets("Working Sheet")
    With .Range("F2:F" & .Range("A" & .Rows.Count).End(xlUp).Row)
        .Formula = "=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE(B2,1,FIND(""/"",B2,FIND(""/"",B2))-2,""""),"""""",""""),""*"","" ""),"" "",REPT("" "",100)),100))" 
        .Value = .Value
     End With
End With


This below code by your end is working perfect, so have replaced it with above.
VBA Code: 
With Worksheets("Working Sheet")
  .Range("G1").Value = "IfAlias Remort Interface"
  With .Range("G2:G" & .Range("A" & .Rows.Count).End(xlUp).Row)
    .Formula2 = "=LET(c,MID(B2,SEQUENCE(LEN(B2)),1),s,CONCAT(IF(ISNUMBER(-c),c,IF(c=""/"",c,REPT("" "",100)))),TRIM(MID(s,FIND(""/"",s)-1,100)))"
'    .Value = .Value
  End With
End With


This is the actual result I am look out for Acutal Result 2 is all done column F expect for 100/0/0/22, but need some modification on column C , Acutal Result 1 column D.

Book1
ABCDEFG
1Actual DataEg 1Acutal Result 1Eg 2Acutal Result 2
2***connected to CoreNCS8-MRSTRDEF-1 Hu0/4/0/25 Link Monitoring PRN 116933***CoreNCS8-MRSTRDEF-1CoreNCS8-MRSTRDEF-10/4/0/25 0/4/0/25
3**Connected to MetroCore-PTX5K-Abar-422-1 et-7/1/7**MetroCore-PTX5K-Abar-422-1PTX5K-Abar-422-17/1/77/1/7
4**Connected to MetroCore-PTX5K-Bur-601-1 et-7/1/9** //High-Latancy_Link_offloaded_in_upload_directionMetroCore-PTX5K-Bur-601-1PTX5K-Bur-601-17/1/97/1/9
5** Connected to PE-AggX16A-Abha-701-2 Gi3/0/11 **PE-AggX16A-Abha-701-2PE-AggX16A-Abha-701-23/0/11 3/0/11
6** Connected to PE-AggX16A-Kham-702-1 Gi16/1/10 **PE-AggX16A-Kham-702-1PE-AggX16A-Kham-702-16/1/10 6/1/10
7** Connected to PE-AggX16A-Abha-701-2 Gi4/1/10 **PE-AggX16A-Abha-701-2PE-AggX16A-Abha-701-24/1/10 4/1/10
8** Connected to PE-AggX16A-Kham-702-1 Gi3/0/11 **PE-AggX16A-Kham-702-1PE-AggX16A-Kham-702-13/0/11 3/0/11
9** Connected to PE-Agg22-Hut-111-5 Te 0/1/0/11 **PE-Agg22-Hut-111-5PE-Agg22-Hut-111-50/1/0/11 0/1/0/11
10** Connected to PE-AggX16-Bil-711-1 Gi1/0/6 **_METRIC-RAISED_IN-UPLOAD-DIRECTIONPE-AggX16-Bil-711-1PE-AggX16-Bil-711-11/0/6 _1/0/6
11**connected to PE-AggX16-Bil-711-1 GigabitEthernet1/0/4**PE-AggX16-Bil-711-1PE-AggX16-Bil-711-11/0/41/0/4
12**connected to PE-AggX16-Bil-711-1 GigabitEthernet2/0/3**PE-AggX16-Bil-711-1PE-AggX16-Bil-711-12/0/32/0/3
13** Connected to PE-AggX16-Bil-711-1 Gi1/0/10 **PE-AggX16-Bil-711-1PE-AggX16-Bil-711-11/0/10 1/0/10
14** Connected to PE-AggX16-Bil-711-1 Gi1/0/11 **PE-AggX16-Bil-711-1PE-AggX16-Bil-711-11/0/11 1/0/11
15**Connected to CoreNCS-Baha-712-1 TenG 0/6/0/8**CoreNCS-Baha-712-1CoreNCS-Baha-712-10/6/0/80/6/0/8
16**Connected to CoreNCS-Baha-712-1 TenG 0/6/0/24**CoreNCS-Baha-712-1CoreNCS-Baha-712-10/6/0/240/6/0/24
17**Connected to CoreNCS-Abha-701-1 Te0/6/0/45**CoreNCS-Abha-701-1CoreNCS-Abha-701-10/6/0/450/6/0/45
18**Connected to CoreNCS-Abha-701-1 TenG 0/6/0/48**CoreNCS-Abha-701-1CoreNCS-Abha-701-10/6/0/480/6/0/48
19**UPE-UPLINK-Connected to PE-Agg22-Mar-231-5 Interface Te0/0/1/9**PE-Agg22-Mar-231-5PE-Agg22-Mar-231-50/0/1/90/0/1/9
20**connected to PE-Agg22-Mar-231-5 Interface Te0/1/1/7****connected to PE-Agg22-Mar-231PE-Agg22-Mar-231-50/1/1/70/1/1/7
21** Connected to PE-AggX16A-Kham-702-1 interface GigabitEthernet3/1/4 **PE-AggX16A-Kham-702-1PE-AggX16A-Kham-702-13/1/4 3/1/4
22** Connected to PE-Agg22-Aziz-223-6 Te0/3/0/17**PE-Agg22-Aziz-223-6PE-Agg22-Aziz-223-60/3/0/170/3/0/17
23** Connected to PE-Agg22-Aziz-223-6 Te0/3/0/9** **PE-Agg22-Aziz-223-6PE-Agg22-Aziz-223-60/3/0/90/3/0/9
24"Connected to UPE3-AIRPEAAC-1 Migreated to PRE-AGG9K-387-00-000-1 Gi100/0/0/22"UPE3-AIRPEAAC-1UPE3-AIRPEAAC-10/0/0/22"0/0/0/22100/0/0/22
25** Connected to PE-AggX16-Muha-751-1 Gi1/8/0/4 **METRIC_OFFLOAD_UPLOAD_SEP_2022*PE-AggX16-Muha-751-1PE-AggX16-Muha-751-11/8/0/4 1/8/0/4
26** Connected to PE-AggX16A-Abha-701-2 Ge4/1/6 **_METRIC-RAISED_IN-UPLOAD-DIRECTIONPE-AggX16A-Abha-701-2PE-AggX16A-Abha-701-24/1/6 _4/1/6
27** Connected to PE-AggX16A-Abha-701-2 Gi4/0/2 **_METRIC-RAISED_IN-UPLOAD-DIRECTIONPE-AggX16A-Abha-701-2PE-AggX16A-Abha-701-24/0/2 _4/0/2
28** Connected to PE-Agg22-Mar-231-5 interface TenGigabitEthernet0/2/0/4 **PE-Agg22-Mar-231-5PE-Agg22-Mar-231-50/2/0/4 0/2/0/4
29** Connected to PE-Agg22-Waj-232-3 Te0/1/0/15 **PE-Agg22-Waj-232-3PE-Agg22-Waj-232-30/1/0/15 0/1/0/15
30** Connected to PE-AggX16-Bis-709-1 Gi1/0/7** METRIC_OFFLOAD_UPLOAD_SEP_2022PE-AggX16-Bis-709-1PE-AggX16-Bis-709-11/0/7 M1/0/7
Sheet1
Cell Formulas
RangeFormula
E2:E30E2=SUBSTITUTE(SUBSTITUTE(LEFT(REPLACE(B2,1,FIND("/",B2,FIND("/",B2))-2,""),9),"*","")," /","")
F2:F30F2=LET(c,MID(B2,SEQUENCE(LEN(B2)),1),s,CONCAT(IF(ISNUMBER(-c),c,IF(c="/",c,REPT(" ",100)))),TRIM(MID(s,FIND("/",s)-1,100)))
C2:C30C2=IFERROR(IF((TEXTBEFORE(TEXTAFTER(B2," to ")," "))="",TRIM(LEFT(SUBSTITUTE(B2,"-",REPT(" ",1000),LEN(B2)-LEN(SUBSTITUTE(B2,"-",""))),1000)),TEXTBEFORE(TEXTAFTER(B2," to ")," ")),TRIM(LEFT(SUBSTITUTE(B2,"-",REPT(" ",1000),LEN(B2)-LEN(SUBSTITUTE(B2,"-",""))),1000)))
 
Last edited:
Upvote 0
vmjan02 said:
Yes result should be 100/0/0/22, you are right
Click to expand...
That is not a result that I suggested. Is that the result that you do expect or is that yet another error with the expected result?


vmjan02 said:
This is the actual result I am look out for Acutal Result 2 is all done column F expect for 100/0/0/22, but need some modification on column C , Acutal Result 1 column D.
Click to expand...
I have no idea what that means. Columns C & D seem to have nothing to do with what has been discussed earlier in the thread.
I am now more confused that ever about what results you do actually expect.

Please post some sample data with the expected results only, manually entered and not by formula.
 
Upvote 0
ok ,np yes aggred eariler you had ask am i asking the right result, but do to huge pressure and to complete the taks i missed this part of 100/0/0/22 insed I missed typed it as 0/0/0/22

can this be modify to get the result

VBA Code: 
=LET(c,MID(B2,SEQUENCE(LEN(B2)),1),s,CONCAT(IF(ISNUMBER(-c),c,IF(c="/",c,REPT(" ",100)))),TRIM(MID(s,FIND("/",s)-1,100)))
 
Upvote 0
Ok here you go,

Book1
ABCDEF
1Actual DataAcutal Result 1 FormulaAcutal Result 1Acutal Result 2 FormulaAcutal Result 2
2***connected to CoreNCS8-MRSTRDEF-1 Hu0/4/0/25 Link Monitoring PRN 116933***CoreNCS8-MRSTRDEF-10/4/0/25
3**Connected to MetroCore-PTX5K-Abar-422-1 et-7/1/7**PTX5K-Abar-422-17/1/7
4**Connected to MetroCore-PTX5K-Bur-601-1 et-7/1/9** //High-Latancy_Link_offloaded_in_upload_directionPTX5K-Bur-601-17/1/9
5** Connected to PE-AggX16A-Abha-701-2 Gi3/0/11 **PE-AggX16A-Abha-701-23/0/11
6** Connected to PE-AggX16A-Kham-702-1 Gi16/1/10 **PE-AggX16A-Kham-702-16/1/10
7** Connected to PE-AggX16A-Abha-701-2 Gi4/1/10 **PE-AggX16A-Abha-701-24/1/10
8** Connected to PE-AggX16A-Kham-702-1 Gi3/0/11 **PE-AggX16A-Kham-702-13/0/11
9** Connected to PE-Agg22-Hut-111-5 Te 0/1/0/11 **PE-Agg22-Hut-111-50/1/0/11
10** Connected to PE-AggX16-Bil-711-1 Gi1/0/6 **_METRIC-RAISED_IN-UPLOAD-DIRECTIONPE-AggX16-Bil-711-11/0/6
11**connected to PE-AggX16-Bil-711-1 GigabitEthernet1/0/4**PE-AggX16-Bil-711-11/0/4
12**connected to PE-AggX16-Bil-711-1 GigabitEthernet2/0/3**PE-AggX16-Bil-711-12/0/3
13** Connected to PE-AggX16-Bil-711-1 Gi1/0/10 **PE-AggX16-Bil-711-11/0/10
14** Connected to PE-AggX16-Bil-711-1 Gi1/0/11 **PE-AggX16-Bil-711-11/0/11
15**Connected to CoreNCS-Baha-712-1 TenG 0/6/0/8**CoreNCS-Baha-712-10/6/0/8
16**Connected to CoreNCS-Baha-712-1 TenG 0/6/0/24**CoreNCS-Baha-712-10/6/0/24
17**Connected to CoreNCS-Abha-701-1 Te0/6/0/45**CoreNCS-Abha-701-10/6/0/45
18**Connected to CoreNCS-Abha-701-1 TenG 0/6/0/48**CoreNCS-Abha-701-10/6/0/48
19**UPE-UPLINK-Connected to PE-Agg22-Mar-231-5 Interface Te0/0/1/9**PE-Agg22-Mar-231-50/0/1/9
20**connected to PE-Agg22-Mar-231-5 Interface Te0/1/1/7**PE-Agg22-Mar-231-50/1/1/7
21** Connected to PE-AggX16A-Kham-702-1 interface GigabitEthernet3/1/4 **PE-AggX16A-Kham-702-13/1/4
22** Connected to PE-Agg22-Aziz-223-6 Te0/3/0/17**PE-Agg22-Aziz-223-60/3/0/17
23** Connected to PE-Agg22-Aziz-223-6 Te0/3/0/9** **PE-Agg22-Aziz-223-60/3/0/9
24"Connected to UPE3-AIRPEAAC-1 Migreated to PRE-AGG9K-387-00-000-1 Gi100/0/0/22"UPE3-AIRPEAAC-1100/0/0/22
25** Connected to PE-AggX16-Muha-751-1 Gi1/8/0/4 **METRIC_OFFLOAD_UPLOAD_SEP_2022*PE-AggX16-Muha-751-11/8/0/4
26** Connected to PE-AggX16A-Abha-701-2 Ge4/1/6 **_METRIC-RAISED_IN-UPLOAD-DIRECTIONPE-AggX16A-Abha-701-24/1/6
27** Connected to PE-AggX16A-Abha-701-2 Gi4/0/2 **_METRIC-RAISED_IN-UPLOAD-DIRECTIONPE-AggX16A-Abha-701-24/0/2
28** Connected to PE-Agg22-Mar-231-5 interface TenGigabitEthernet0/2/0/4 **PE-Agg22-Mar-231-50/2/0/4
29** Connected to PE-Agg22-Waj-232-3 Te0/1/0/15 **PE-Agg22-Waj-232-30/1/0/15
30** Connected to PE-AggX16-Bis-709-1 Gi1/0/7** METRIC_OFFLOAD_UPLOAD_SEP_2022PE-AggX16-Bis-709-11/0/7
Sheet1
Cell Formulas
RangeFormula
F2:F23,F25:F30F2=LET(c,MID(B2,SEQUENCE(LEN(B2)),1),s,CONCAT(IF(ISNUMBER(-c),c,IF(c="/",c,REPT(" ",100)))),TRIM(MID(s,FIND("/",s)-1,100)))
 
Upvote 0
Thanks for the extra sample data ... BUT ... you didn't ever do this ..
Peter_SSs said:
Please explain clearly, in words, exactly what should be extracted from the text.
Click to expand...
... and so now I cannot understand what the logic is as to why the leading digit "1" is not included in the wanted result in row 6 (see yellow below) but the leading digits "10" are included in the wanted result in row 24 (green).

1700078485422.png
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Search and Replace
Replies
3
Views
303
vmjan02
V
V
  • Question Question
Column Output 2
Replies
7
Views
333
vmjan02
V
V
  • Solved
If function now working
Replies
4
Views
414
vmjan02
V
gtd526
  • Solved
Get the Row number for Matched cell value
Replies
2
Views
406
gtd526
gtd526
F
  • Solved
Formula to get unique values based on 3 columns?
Replies
3
Views
434
Fractalis
F

Forum statistics

Threads
1,223,711
Messages
6,174,028
Members
452,542
Latest member
Bricklin

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