Remove text appearing between two characters - multiple instances - Excel -

-

in microsoft excel file, have text in rows appears this:

1. rc8 {[%emt 0:00:05]} rxc8 {[%emt 0:00:01]} 2. rxc8 {[%emt 0:00:01]} qxc8 {} 3. qe7#  1-0

i need remove text appearing within flower brackets { , }, including brackets themselves.

in above example, there 3 instances of such flower brackets. rows might have more that.

i tried =mid(left(a2,find("}",a2)-1),find("{",a2)+1,len(a2))

this outputs to: {[%emt 0:00:05]}. see first instance of text between flower brackets.

and if use within substitute this: =substitute(a2,mid(left(a2,find("}",a2)),find("{",a2),len(a2)),"")

i output this:

1. rc8 rxc8 {[%emt 0:00:01]} 2. rxc8 {[%emt 0:00:01]} qxc8 {} 3. qe7# 1-0

if have noticed, 1 instance removed. how make work instances? thanks.

it not easy without vba, there still way.

either (as suggested yu_ominae) use formula , auto-fill it:

=iferror(substitute(a2,mid(left(a2,find("}",a2)),find("{",a2),len(a2)),""),a2)

another way iterative calculations (go options -> formulas -> check "enable iterative calculations" button)
in 1 cell, need 1 helper-cell (for example use c1) , use formula in b2 , auto-fill down:

=if($c$1,a2,iferror(substitute(b2,mid(left(b2,find("}",b2)),find("{",b2),len(b2)),""),b2))

put "1" in c1 , formulas in b:b show values of a:a. go c1 , hit del-key several times (you see "{}"-parts disappearing) till looks want it.

edit: via vba without regex can put module:

public function delbrc(byval str string) string   while instr(str, "{") > 0 , instr(str, "}") > instr(str, "{")     str = left(str, instr(str, "{") - 1) & mid(str, instr(str, "}") + 1)   wend   delbrc = trim(str) end function

and in worksheet directly use:

=delbrc(a2)

if still have questions, ask ;)


Comments

Post a Comment

Popular posts from this blog

javascript - Feed FileReader from server side files -

-
i´m starting customize/improve old audio editor project. can import audio tracks canvas via drag&drop computer. thing use audio tracks stored in server clicking on list of available tracks... instead of use <input type="file"> tags. how can read server side files filereader?ajax perhaps? in advance. this code file reader: player.prototype.loadfile = function(file, el) { //console.log(file); var reader = new filereader, filetypes = ['audio/mpeg', 'audio/mp3', 'audio/wave', 'audio/wav'], = this; if (filetypes.indexof(file.type) < 0) { throw('unsupported file format!'); } reader.onloadend = function(e) { if (e.target.readystate == filereader.done) { // done == 2 $('.progress').children().width('100%'); var onsuccess = function(audiobuffer) { $(el).trigger('audiee:fileloaded', [audiobuffer, file]); ...
Read more

How to Change swipe Tab Title color in java Android -

-
by default, view pager(swipe tabs) tab title color white want change orange. can 1 me please or share if have tutorial. thanks... use style in styles.xml , assign style tab layout. <style name="mymaterialtheme.tablayout" parent="widget.design.tablayout"> <item name="tabindicatorcolor">@android:color/white</item> <item name="tabindicatorheight">3dp</item> <item name="tabtextappearance">@style/mymaterialtheme.tablayout.textappearance</item> <item name="tabselectedtextcolor">@android:color/white</item> <item name="android:background">@color/colorprimary</item> </style> <style name="mymaterialtheme.tablayout.textappearance" parent="textappearance.design.tab"> <item name="android:textsize">14sp</item> <item name="android:textcolor">@color/tab_text_inactive</item> ...
Read more

Using globs in Perl replace one liner in TCL script -

-
i want run 1 perl 1 liner in tcl script below: exec perl -i -pe {s/substring/replacing_string/g} testfile; this works fine. but, if want modify files below: exec perl -i -pe {s/substring/replacing_string/g} *; it gives me error message: can't open '*': no such file or directory. while executing exec perl -i -pe {s/substring/replacing_string/g} *; i tried bracing '*', did not solve problem. requesting help... assuming files a , b , , c present in current working directory, executing echo * in shell prints a b c . because shell command evaluator recognizes wildcard characters , splices in list of 0 or more file names wildcard expression found. tcl's command evaluator not recognize wildcard characters, passes them unsubstituted command invoked. if command can work wildcards so. exec command doesn't, means pass wildcard expression shell command named command string. testing this, get % exec echo * * because asked shell exe...
Read more