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!
 

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,301
=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
358
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:

Forum statistics

Threads
1,077,849
Messages
5,336,734
Members
399,100
Latest member
darcob

Some videos you may like

This Week's Hot Topics

Top