Extract Multiple text that contains - in a Single cell to individual cells

[amrans20]

Hello, this is my 1st post so please go gentle on me if this question has already been asked. I've search and found some similarities with extracting an email but unable to adapt what I've found with what I need so hopefully someone can help.

What I would like to do is to extract multiple text that contains "-".

example (column A)	desired result (column B)
Swap from TSS-KGLR-EN1-TRTT to TSS-KGL-EN1-TRT	TSS-KGLR-EN1-TRTT TSS-KGL-EN1-TRT
Migrate from TSS-KGLR-EN1-TRTT and TSS-KGL-EN1-TR8 to TSS-KGL-EN1-TRT	TSS-KGLR-EN1-TRTT TSS-KGL-EN1-TR8 TSS-KGL-EN1-TRT

Any help would be greatly appreciated. Thank you for you time.

 

[Peter_SSs]

Welcome to the MrExcel board!

You could try this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (use the icon at the top right of the code pane

) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function Dashes(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\b.[^A-Z\-]+\b"
    Dashes = Application.Trim(.Replace(s, " "))
  End With
End Function

amrans20 2020-02-23 1.xlsm
	A	B
1
2	Swap from TSS-KGLR-EN1-TRTT to TSS-KGL-EN1-TRT	TSS-KGLR-EN1-TRTT TSS-KGL-EN1-TRT
3	Migrate from TSS-KGLR-EN1-TRTT and TSS-KGL-EN1-TR8 to TSS-KGL-EN1-TRT	TSS-KGLR-EN1-TRTT TSS-KGL-EN1-TR8 TSS-KGL-EN1-TRT
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=Dashes(A2)

 

[amrans20]

Welcome to the MrExcel board!

You could try this user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (use the icon at the top right of the code pane View attachment 7344) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function Dashes(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "\b.[^A-Z\-]+\b"
    Dashes = Application.Trim(.Replace(s, " "))
  End With
End Function

amrans20 2020-02-23 1.xlsm
	A	B
1
2	Swap from TSS-KGLR-EN1-TRTT to TSS-KGL-EN1-TRT	TSS-KGLR-EN1-TRTT TSS-KGL-EN1-TRT
3	Migrate from TSS-KGLR-EN1-TRTT and TSS-KGL-EN1-TR8 to TSS-KGL-EN1-TRT	TSS-KGLR-EN1-TRTT TSS-KGL-EN1-TR8 TSS-KGL-EN1-TRT
Sheet1

Cell Formulas
Range	Formula
B2:B3	B2	=Dashes(A2)

Hello Peter,

Thank you very much for helping me with your solution. so quick and works exactly with what i need, awesome ?

 

[amrans20]

Hi,

after running quite some of the data with your code, i found some example of cell that didn't work:

example	result
Integration_Protocol Activation TPDR-EN1 at SSY-GRSS1K-EN1-AR8x	Integration_Protocol TPDR-EN1 SSY-GRSS1K-EN1-AR8x
Upgrade from 1x1G to 1x10G BTC-EN1 at BVG-CERT-AN1-ARS8	1x1G 1x10G BTC-EN1 BVG-CERT-AN1-ARS8
Upgrade link SSY-KYN-PN3-SR14s >< SRTY-GW1-e8k-1	SSY-KYN-PN3-SR14s SRTY-GW1

could you please help me again with modified code that would get:

desired result

TPDR-EN1 at SSY-GRSS1K-EN1-AR8x

BTC-EN1 BVG-CERT-AN1-ARS8

SSY-KYN-PN3-SR14s SRTY-GW1-e8k-1

Thank you :D much appreciated

 

[Peter_SSs]

In your original samples, all the "-" texts were upper case, so I used that fact in my suggestion.

desired result
TPDR-EN1 at SSY-GRSS1K-EN1-AR8x

I'm hoping that "at" is a mistake?

If so, try changing the Pattern line to this

.Pattern = "(^| )[^\-]+( |$)"

 

[amrans20]

In your original samples, all the "-" texts were upper case, so I used that fact in my suggestion.

I'm hoping that "at" is a mistake?

If so, try changing the Pattern line to this

.Pattern = "(^| )[^\-]+( |$)"

Thank you again Peter, the modified pattern line works very great, ? ?

"at" in desired result above is my mistake ?

 

[Peter_SSs]

Thank you again Peter, the modified pattern line works very great, ? ?

Cheers. Thanks for the confirmation.