Getting out numbers

V

vmjan02

Well-known Member
Joined
Aug 15, 2012
Messages
1,059
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
maybe this?

=SUBSTITUTE(LEFT(REPLACE(A2,1,FIND("/",A2,FIND("/",A2))-2,""),9),"*","")

put this on Cell b2, assuming that a2 is the first data to be converted
 
Upvote 0
Armstrong_N14 said:
maybe this?

=SUBSTITUTE(LEFT(REPLACE(A2,1,FIND("/",A2,FIND("/",A2))-2,""),9),"*","")

put this on Cell b2, assuming that a2 is the first data to be converted
Click to expand...
You picked up a " /" on Row 4's result, so you need to SUBSTITUTE it away...
Excel Formula: 
=SUBSTITUTE(SUBSTITUTE(LEFT(REPLACE(A2,1,FIND("/",A2,FIND("/",A2))-2,""),9),"*","")," /","")
You can modify this formula to spill the entire result listing from a single formula...
Excel Formula: 
=MAP(A2:A19,LAMBDA(x,SUBSTITUTE(SUBSTITUTE(LEFT(REPLACE(x,1,FIND("/",x,FIND("/",x))-2,""),9),"*","")," /","")))
 
Upvote 0
Thanks for a great and quick response, but I am very very sorry the actual data is in column A, once again sorry for the

I am very sorry for my wrong post earlier for the input data in column A, now in below table the actual data is in. If the formula can be modified it will be a great help.




Book2
BCD
1Actual DataEg 1Eg 2
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 M
Sheet1
Cell Formulas
RangeFormula
D2:D117D2=MAP(B2:B117,LAMBDA(x,SUBSTITUTE(SUBSTITUTE(LEFT(REPLACE(x,1,FIND("/",x,FIND("/",x))-2,""),9),"*","")," /","")))
C2:C30C2=SUBSTITUTE(SUBSTITUTE(LEFT(REPLACE(B2,1,FIND("/",B2,FIND("/",B2))-2,""),9),"*","")," /","")
Dynamic array formulas.
 
Upvote 0
Rick Rothstein said:
You picked up a " /" on Row 4's result, so you need to SUBSTITUTE it away...
Excel Formula: 
=SUBSTITUTE(SUBSTITUTE(LEFT(REPLACE(A2,1,FIND("/",A2,FIND("/",A2))-2,""),9),"*","")," /","")
You can modify this formula to spill the entire result listing from a single formula...
Excel Formula: 
=MAP(A2:A19,LAMBDA(x,SUBSTITUTE(SUBSTITUTE(LEFT(REPLACE(x,1,FIND("/",x,FIND("/",x))-2,""),9),"*","")," /","")))
Click to expand...
I am trying this but no help


Excel Formula: 
=MAP(B2:B500, LAMBDA(x, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LEFT(REPLACE(x, 1, FIND("/", x, FIND("/", x)) - 2, ""), 9), "*", ""), " /", ""), "_", ""), """", ""), " M", ""), "PRN",""))
 
Upvote 0
Try this

Book1
BC
1Actual DataEg 1
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
Sheet1
Cell Formulas
RangeFormula
C2:C30C2=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE(B2,1,FIND("/",B2,FIND("/",B2))-2,""),"""",""),"*"," ")," ",REPT(" ",100)),100))
 
Upvote 0
Can you confirm what the correct expected result is for this line in your post #4 sample data?

1699966042020.png
 
Last edited:
Upvote 0
Phuoc said:
Try this

Book1
BC
1Actual DataEg 1
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
Sheet1
Cell Formulas
RangeFormula
C2:C30C2=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(REPLACE(B2,1,FIND("/",B2,FIND("/",B2))-2,""),"""",""),"*"," ")," ",REPT(" ",100)),100))
Click to expand...

this code is giving me blank and manual formula is working perfect, any solution for it

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
You must log in or register to reply here.

Similar threads

V
  • Solved
Search and Replace
Replies
3
Views
192
vmjan02
V
V
  • Question
Column Output 2
Replies
7
Views
204
vmjan02
V
V
  • Solved
If function now working
Replies
4
Views
281
vmjan02
V
gtd526
  • Solved
Get the Row number for Matched cell value
Replies
2
Views
231
gtd526
gtd526
N
  • Question
Easiest way to get total.
Replies
3
Views
275
awoohaw
awoohaw

Forum statistics

Threads
1,215,076
Messages
6,122,988
Members
449,093
Latest member
Mr Hughes

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