# Alternative to Textbefore Function



## NorthbyNorthwest (Jan 3, 2023)

Hi, everyone.  I created a workbook for another person's use.  The workbook includes the new textbefore function.  Unfortunately, it appears the user's version of Excel does not support this function.  The formula is as follows:
=TEXTBEFORE(E4," (",-1,,,E4)

The workbook has a list of names followed by unneeded remark in parentheses.  Example"  SMITH, JOHN A (1234).  The formula above removed (1234).  Is there older function or combination of older functions I can use to achieve the same result?  Note the remark in parentheses varies in length.  It isn't always four characters within the parentheses or six characters including parentheses.


----------



## Dave Patton (Jan 3, 2023)

try

T202301a.xlsmEFG1234SMITH, JOHN A (1234)SMITH, JOHN A 51bCell FormulasRangeFormulaF4F4=LEFT(E4,FIND("(",E4)-1)


----------



## etaf (Jan 3, 2023)

how about
=LEFT(E4,FIND("(",E4,1)-1)


----------



## NorthbyNorthwest (Jan 3, 2023)

etaf said:


> how about
> =LEFT(E4,FIND("(",E4,1)-1)


Thanks, etaf.  Left worked perfectly.  I have a second formula in the workbook that cleans up names by removing a hyphen and everything that follows after the last and first names.  I tried swapping out the parentheses in your formula with a hyphen.  I got a VALUE error.  Could you help with this too?


----------



## etaf (Jan 3, 2023)

an example would help 

also Dave Patton provided the same answer

how about

Book2EF1234 SMITH, JOHN A (1234 SMITH, JOHN A 5 SMITH, JOHN A  - (1235 SMITH, JOHN A  Sheet1Cell FormulasRangeFormulaF4F4=LEFT(E4,SEARCH("(",E4,1)-1)F5F5=LEFT(E5,SEARCH("-",E5,1)-1)


----------



## NorthbyNorthwest (Jan 3, 2023)

etaf said:


> an example would help
> 
> also Dave Patton provided the same answer
> 
> ...


Example:  SMITH, JOHN A - 1234
I also want to get rid of the hyphen and what follows.  I tried using the left function as you did.  It removed the "1234" but not the hyphen or space following the hyphen.


----------



## etaf (Jan 3, 2023)

has in my example
E5

=LEFT(E4,SEARCH("-",E4,1)-1)

to get rid of any trailing spaces
=TRIM(LEFT(E4,SEARCH("-",E4,1)-1))

Book2EF1234 SMITH, JOHN A (1234 SMITH, JOHN A 5 SMITH, JOHN A  - (1235 SMITH, JOHN A  678SMITH, JOHN A - 1234SMITH, JOHN ASheet1Cell FormulasRangeFormulaF4F4=LEFT(E4,SEARCH("(",E4,1)-1)F5F5=LEFT(E5,SEARCH("-",E5,1)-1)F8F8=TRIM(LEFT(E8,SEARCH("-",E8,1)-1))


----------



## NorthbyNorthwest (Jan 3, 2023)

etaf said:


> has in my example
> E5
> 
> =LEFT(E4,SEARCH("-",E4,1)-1)
> ...


Yes that worked too.  Thanks again, etaf.  


Dave Patton said:


> try
> 
> T202301a.xlsmEFG1234SMITH, JOHN A (1234)SMITH, JOHN A 51bCell FormulasRangeFormulaF4F4=LEFT(E4,FIND("(",E4)-1)


Thanks for the response, Dave.  Appreciate all.


----------



## etaf (Jan 3, 2023)

you are welcome


----------

