Formula for remove all into ()

jamiguel77

Active Member
Joined
Feb 14, 2006
Messages
374
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
  2. Web
hi all

i have:

A1=C811D(3)
A2=D807D(1)
A3=D810D(10)AC TR
A4=D210D(10)

i want remove all into () sample of result:

B1=C811D
B2=D807D
B3=D810DAC TR
B4=D210D


any advice?

thanks
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
with PowerQuery
Code:
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "src", Splitter.SplitTextByDelimiter("(", QuoteStyle.Csv), {"src.1", "src.2"}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Split Column by Delimiter", {{"src.2", each Text.AfterDelimiter(_, ")"), type text}}),
    #"Merged Columns" = Table.CombineColumns(#"Extracted Text After Delimiter",{"src.1", "src.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Result")
in
    #"Merged Columns"[/SIZE]

srcResult
C811D(3)C811D
D807D(1)D807D
D810D(10)AC TRD810DAC TR
D210D(10)D210D
 
Upvote 0
@Aladin Akyurek Worked... only added: =if.error(your formula,A1); @sandy666: always you do with power Query, interesting, for learn.. much thanks (i am parctice more power Query)....

Thanks
 
Last edited:
Upvote 0
Hi,

This formula will also work:


Book1
AB
1C811D(3)C811D
2D807D(1)D807D
3A123B456A123B456
4D810D(10)AC TRD810DAC TR
5D210D(10)D210D
Sheet226
Cell Formulas
RangeFormula
B1=IFERROR(REPLACE(A1,FIND("(",A1),FIND(")",A1)-FIND("(",A1)+1,""),A1)
 
Upvote 0
Also since the left parenthesis always starts at the 6th character, per the sample:

=IF(MID(A1,6,1)="(",REPLACE(A1,6,FIND(")",A1)-5,""),A1)
 
Upvote 0
Here is another one
=SUBSTITUTE(A1,LEFT(MID(A1,FIND("(",A1),FIND(")",A1)),4),"")


Book1
AB
1C811D(3)C811D
2D807D(1)D807D
3D810D(10)AC TRD810DAC TR
4D210D(10)D210D
Sheet2
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

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