Remove before first blank and after " ("

[everblazing]

HI

Im trying to have the below two formulas combined so that i can remove the first part of text before space and last of a text in same cell after " (".

appreciate any help.


text in cell A2

200593 Senior Products Systems (United)

<tbody>
</tbody>

the formula in B2 this cuts out anything before space.
=MID(A2,FIND(" ",A2)+1,256)

this cuts out anything with " (" and after
=LEFT(B15197,FIND(" (",B15197)-1)

I need them combined.

thanks

 

[ttray33y]

hi

im trying to have the below two formulas combined so that i can remove the first part of text before space and last of a text in same cell after " (".

Appreciate any help.


Text in cell a2

200593 senior products systems (united)

<tbody>
</tbody>

the formula in b2 this cuts out anything before space.
=mid(a2,find(" ",a2)+1,256)

this cuts out anything with " (" and after
=left(b15197,find(" (",b15197)-1)

i need them combined.

Thanks

??

=left(mid(a2,find(" ",a2)+1,256),find(" (",mid(a2,find(" ",a2)+1,256))-1)

 

[everblazing]

wow that works, how did you manage that? i'd love a basic explanation. what was the first step you took to put the two together?

so if i was tp put that in a macro that autofills from the first blank cell in a column to the last empty cell in a collumn is it best to use .address range?

Regards.

 

[Aladin Akyurek]

Try...

=TRIM(REPLACE(LEFT(A2,FIND("(",A2)-1),1,FIND(" ",A2)-1,""))

 

[ttray33y]

wow that works, how did you manage that? i'd love a basic explanation. what was the first step you took to put the two together?

so if i was tp put that in a macro that autofills from the first blank cell in a column to the last empty cell in a collumn is it best to use .address range?

Regards.

given from your post

the formula in B2 this cuts out anything before space.
=MID(A2,FIND(" ",A2)+1,256)

(modified this) this cuts out anything with " (" and after
=LEFT(A2,FIND(" (",A2)-1)

used your MID(A2,FIND(" ",A2)+1,256) without the equal sign of course,
and inserted that to =LEFT(MID(A2,FIND(" ",A2)+1,256),FIND(" (",MID(A2,FIND(" ",A2)+1,256))-1)

make sense now?

for the macro part, i can make out without a formula, omitting those characters.

 

[shaowu459]

Though above answeres already solved your problem, I'd like to provide more options, please try the following:

=VLOOKUP(" *",MID(A2,ROW($2:99)-{1,0},FIND("(",A2)-FIND(" ",A2)-2),2,)
(Ctrl+Shift+Enter)

=REPLACE(LEFT(A2,FIND("(",A2)-2),1,FIND(" ",A2),"")

=TRIM(MID(SUBSTITUTE(A2,"(",REPT(" ",99)),FIND(" ",A2),99))

=TRIM(MID(A2,FIND(" ",A2),SUM(FIND({" ","("},A2)*{-1,1})))