# Formula? Extract text between 2 characters

#### boilerup

##### New Member
I need a formula to help the text below...
Company Name (D-0003504825)

to look like this
0003504825_Company Name

On my excel worksheet, all text inside parentheses include "D" then "-" then 10 numbers.

Thanks!

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### boilerup

##### New Member
Note: cant simply do formula that finds characters between "-" and ")" because some cells look like this...
Company-Name (D-0003594819)

#### Special-K99

##### Well-known Member
=MID(A1,FIND("(",A1)+3,10)&"_"&LEFT(A1,FIND("(",A1)-2)

Need more example data to ensure this works properly

#### mikerickson

##### MrExcel MVP
Would =MID(A1, LEN(A1)-10, 10) do what you want?

#### AlKey

Removed post.

Last edited:

##### Active Member
HI
What about this for one cell (can easily amended for range of cells If OP likes it)
Suppose the format is always xxxxx xxxxx (D-xxxxxxxxxx)
Code:
``````Sub teste()
e = Split(Split(Join(Split(Split(Join(Split(Range("a1"), "(D-"), Chr(22)), ")")(0), "(D-"), Chr(22)), ")")(0), Chr(22))
Range("b1") = Trim(e(1)) & "_" & e(0)
End Sub``````
Or
Code:
``````Sub Test2()
With CreateObject("VBScript.RegExp")
.Global = True
.Pattern = "(^\w+.\w+)|(\d+)"
Set spl = .Execute(Range("a1"))
Range("b1") = spl(1) & "_" & spl(0)
End With
End Sub``````

Last edited: