How to remove all text in parentheses from a line of text

[divster27]

Hello,

I receive a datasheet from a supplier which has the artist, title and some further information in a string like this:

Dekker, Desmond / Double Dekker (2LP Gold Coloured)

I've used text to columns to split out Dekker, Desmond and Double Dekker into separate columns, Artist and Title
What i'd like to be able to do is before this create a new column that says:

"everything in parentheses move into another column, and remove the parentheses"

So it would become

ARTIST	TITLE	Notes
Dekker, Desmond​	Double Dekker​	2LP Gold Coloured​

Any help appreciated!

David
Netdiscs

 

[JEC]

Assuming your data starts in A1

Sub jec()
 Dim ar, sp, j As Long, jj As Long
 ar = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 10)
 
 For j = 1 To UBound(ar)
   sp = Split(Replace(Replace(ar(j, 1), ")", ""), "(", " / "), " / ")
   For jj = 0 To UBound(sp)
     ar(j, jj + 1) = sp(jj)
   Next
 Next
 
 Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 10) = ar
End Sub

 

[divster27]

Thanks that looks almost there - just need to move the data to new columns - the code overtypes what is there already

 

[JEC]

Ok, then write the last line as

Range("A1", Range("A" & Rows.Count).End(xlUp)).Offset(,1).Resize(, 10) = ar

 

[divster27]

Hiya - still doing the same as far as i can see
The spreadsheet has column headers as below
I want to move the data to columns J, K and L as below

order	Stock	notes		Format	ARTIST / TITLE​	Barcode	Cat number	Label	Artist​	Title​	Note​
	new	New release LAF exclusive MoV 9.FEB.24.	£23.99	VINYL	OST / Call Me By Your Name (2LP Translucent Pink Coloured)​	8719262033351	MOVATM184T	MUSIC ON VINYL
	new	New release LAF exclusive MoV 9.FEB.24.	£17.90	VINYL	OST / It Follows (1LP Yellow & Green Coloured)​	8719262034020	MOVATM311Y	MUSIC ON VINYL
	new	New release LAF exclusive MoV 9.FEB.24.	£17.90	VINYL	OST / My Policeman (1LP Turquoise Coloured)​	8719262034617	MOVATM366TU	MUSIC ON VINYL
	new	New release LAF exclusive MoV 9.FEB.24.	£19.90	VINYL	Dekker, Desmond / Double Dekker (2LP Gold Coloured)​	8719262029804	MOVLP2483G	MUSIC ON VINYL
	new	New release LAF exclusive MoV 9.FEB.24.	£15.50	VINYL	Drop Nineteens / Delaware (1LP Black)​	0600753949955	MOVLP3319	MUSIC ON VINYL
	new	New release LAF exclusive MoV 9.FEB.24.	£24.90	VINYL	Weather Report / Live In Tokyo (2LP Coloured)​	8719262030947	MOVLP3521C	MUSIC ON VINYL
	new	New release LAF exclusive MoV 9.FEB.24.	£16.50	VINYL	Twenty 4 Seven / I Wanna Show You =30th Ann= (1LP Coloured)​	8719262033825	MOVLP3671C	MUSIC ON VINYL

I'm using the following code

Sub jec()
Dim ar, sp, j As Long, jj As Long
ar = Range("F1", Range("F" & Rows.Count).End(xlUp)).Resize(, 10)

For j = 1 To UBound(ar)
sp = Split(Replace(Replace(ar(j, 1), ")", ""), "(", " / "), " / ")
For jj = 0 To UBound(sp)
ar(j, jj + 1) = sp(jj)
Next
Next

Range("F1", Range("F" & Rows.Count).End(xlUp)).Offset(,1).Resize(, 10) = ar

End Sub

 

[alansidman]

Get and Transform Data on Data Tab

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Replaced Value" = Table.ReplaceValue(Source,"(","/",Replacer.ReplaceText,{"ARTIST / TITLE"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",")","",Replacer.ReplaceText,{"ARTIST / TITLE"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "ARTIST / TITLE", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"ARTIST / TITLE.1", "ARTIST / TITLE.2", "ARTIST / TITLE.3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"ARTIST / TITLE.1", "Artist"}, {"ARTIST / TITLE.2", "Title"}, {"ARTIST / TITLE.3", "Type"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"order", "Stock", "notes", "Column1", "Format", "Barcode", "Cat number", "Label", "Artist", "Title", "Type"})
in
    #"Reordered Columns"

Book6
	A	B	C	D	E	F	G	H	I	J	K
10	order	Stock	notes	Column1	Format	Barcode	Cat number	Label	Artist	Title	Type
11		new	New release LAF exclusive MoV 9.FEB.24.	£23.99	VINYL	8.71926E+12	MOVATM184T	MUSIC ON VINYL	OST	Call Me By Your Name	2LP Translucent Pink Coloured
12		new	New release LAF exclusive MoV 9.FEB.24.	£17.90	VINYL	8.71926E+12	MOVATM311Y	MUSIC ON VINYL	OST	It Follows	1LP Yellow & Green Coloured
13		new	New release LAF exclusive MoV 9.FEB.24.	£17.90	VINYL	8.71926E+12	MOVATM366TU	MUSIC ON VINYL	OST	My Policeman	1LP Turquoise Coloured
14		new	New release LAF exclusive MoV 9.FEB.24.	£19.90	VINYL	8.71926E+12	MOVLP2483G	MUSIC ON VINYL	Dekker, Desmond	Double Dekker	2LP Gold Coloured
15		new	New release LAF exclusive MoV 9.FEB.24.	£15.50	VINYL	6.00754E+11	MOVLP3319	MUSIC ON VINYL	Drop Nineteens	Delaware	1LP Black
16		new	New release LAF exclusive MoV 9.FEB.24.	£24.90	VINYL	8.71926E+12	MOVLP3521C	MUSIC ON VINYL	Weather Report	Live In Tokyo	2LP Coloured
17		new	New release LAF exclusive MoV 9.FEB.24.	£16.50	VINYL	8.71926E+12	MOVLP3671C	MUSIC ON VINYL	Twenty 4 Seven	I Wanna Show You =30th Ann=	1LP Coloured
Sheet2