Formula? Extract text between 2 characters

boilerup

New Member
Joined
Jul 17, 2019
Messages
4
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!
 

Some videos you may like

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
Joined
Jul 17, 2019
Messages
4
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
Joined
Nov 7, 2006
Messages
8,341
=MID(A1,FIND("(",A1)+3,10)&"_"&LEFT(A1,FIND("(",A1)-2)

Need more example data to ensure this works properly
 

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,090,474
Messages
5,414,754
Members
403,543
Latest member
Phx007

This Week's Hot Topics

Top