Extract certain text string from cell

[Beh162]

I have some data that gets pulled in from an import and I'm trying to split the data but I'm running into some confusion

A1
BUF-8(-110)
I need to pull the "BUF" into one cell(b1) and -8 in another (C1)
=Left(A1,3) and it would give me BUF

I ran into a problem when it was 2 characters
NE-2.5(-110)

It will only ever be 2 or 3 characters. The numbers can range from 1 to 5 though

What would be a better approach? Thanks

 

[JoeMo]

If you want to retain the "-" before the number in col C, add: "-"&
before TRIM.
Sheet2

	A	B	C
1	BUF-8(-110)	BUF	8
2	NE-2.5(-110)	NE	2.5

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:83px;"><col style="width:31px;"><col style="width:25px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =TRIM(LEFT(SUBSTITUTE(SUBSTITUTE($A1,"(",""),"-",REPT(" ",100)),100)) C1 =TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,"(",""),"-",REPT(" ",100)),100,100)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Beh162]

That worked great, some options I need to learn more about. Appreciate the help

 

[Rick Rothstein]

If there is a dash (minus sign?) always in front of the first number and that first number is always a single digit, then these formulas should also work for you...

B1: =LEFT(A1,FIND("-",A1)-1)

C1: =MID(A1,FIND("(",A1)-1,1)