Search and Replace

[vmjan02]

Not sure which function to use and any vab code, with actual input column need Output column, need Replace HU an Replace TenG

any suggestion.

	Final OutPut
Input Actual	Output	Replace HU	Replace TenG
***connected to CoreNCS8-MRSTRDEF-1 Hu0/4/0/25 Link Monitoring PRN 116933***	CoreNCS8-MRSTRDEF-1	HundredGigE
**Connected to MetroCore-PTX5K-Abar-422-1 et-7/1/7**	MetroCore-PTX5K-Abar-422-1
**Connected to MetroCore-PTX5K-Bur-601-1 et-7/1/9** //High-Latancy_Link_offloaded_in_upload_direction	MetroCore-PTX5K-Bur-601-1
** Connected to PE-AggX16A-Abha-701-2 Gi3/0/11 **	PE-AggX16A-Abha-701-2
** Connected to PE-AggX16A-Kham-702-1 Gi16/1/10 **	PE-AggX16A-Kham-702-1
** Connected to PE-AggX16A-Abha-701-2 Gi4/1/10 **	PE-AggX16A-Abha-701-2
** Connected to PE-AggX16A-Kham-702-1 Gi3/0/11 **	PE-AggX16A-Kham-702-1
** Connected to PE-Agg22-Hut-111-5 Te 0/1/0/11 **	PE-Agg22-Hut-111-5
** Connected to PE-AggX16-Bil-711-1 Gi1/0/6 **_METRIC-RAISED_IN-UPLOAD-DIRECTION	PE-AggX16-Bil-711-1
**connected to PE-AggX16-Bil-711-1 GigabitEthernet1/0/4**	PE-AggX16-Bil-711-1
**connected to PE-AggX16-Bil-711-1 GigabitEthernet2/0/3**	PE-AggX16-Bil-711-1
** Connected to PE-AggX16-Bil-711-1 Gi1/0/10 **	PE-AggX16-Bil-711-1
** Connected to PE-AggX16-Bil-711-1 Gi1/0/11 **	PE-AggX16-Bil-711-1
**Connected to CoreNCS-Baha-712-1 TenG 0/6/0/8**	CoreNCS-Baha-712-1		TenGigabitEthernet
**Connected to CoreNCS-Baha-712-1 TenG 0/6/0/24**			TenGigabitEthernet
**Connected to CoreNCS-Abha-701-1 Te0/6/0/45**			TenGigabitEthernet
**Connected to CoreNCS-Abha-701-1 TenG 0/6/0/48**			TenGigabitEthernet

 

[Glenn Kennedy]

If data start in a2...

B2:
=TEXTBEFORE(TEXTAFTER(A2:A18," to ")," ")

C2:
=IF(LEFT(TEXTAFTER(TEXTAFTER(A2:A18," to ")," "),1)="H","HundredGigabitEthernet","")

D2:
=IF(LEFT(TEXTAFTER(TEXTAFTER(A2:A18," to ")," "),1)="T","TenGigabitEthernet","")

 

[vmjan02]

If data start in a2...

B2:
=TEXTBEFORE(TEXTAFTER(A2:A18," to ")," ")

C2:
=IF(LEFT(TEXTAFTER(TEXTAFTER(A2:A18," to ")," "),1)="H","HundredGigabitEthernet","")

D2:
=IF(LEFT(TEXTAFTER(TEXTAFTER(A2:A18," to ")," "),1)="T","TenGigabitEthernet","")

perfect it wokred .

 

[vmjan02]

Only think is can i get this as well

Data	Result 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 direction	7/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-DIRECTION	1/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