Formula to get data with specific logic from a string

[PANIGGR]

I have below data in column A, in this string suffixed with data like "(12345/A76D7YF)". I need numeric and alphanumeric character in two different columns. Please note that customer names before above section may also contain (, ) and /. Uniqueness of column A is that after the last open bracket "(", the required numeric and alphanumeric data comes which may help is building any logic/VBA code

JAMES WALKER(614726/8CZNISNS) <colgroup><col width="97"></colgroup><tbody> </tbody>	8CZNISNS	614726
ABC Ltd (306521476/3BAX80ZJ) <colgroup><col width="97"></colgroup><tbody> </tbody>	3BAX80ZJ	306521476
Woodside/Ltd (3953084/MZ0QR1LJ) <colgroup><col width="97"></colgroup><tbody> </tbody>	MZ0QR1LJ	3953084
Smiths) Ltd (467578/A3RJ67K3K) <colgroup><col width="97"></colgroup><tbody> </tbody>	A3RJ67K3K	467578
(Highland/YUYU(5961944/2GN4E1U8) <colgroup><col width="97"></colgroup><tbody> </tbody>	2GN4E1U8	5961944

<tbody>
</tbody>

 

[sandy666]

with Power Query aka Get&Transform

Column1		Column1.1	Column1.2
JAMES WALKER(614726/8CZNISNS)		614726	8CZNISNS
ABC Ltd (306521476/3BAX80ZJ)		306521476	3BAX80ZJ
Woodside/Ltd (3953084/MZ0QR1LJ)		3953084	MZ0QR1LJ
Smiths) Ltd (467578/A3RJ67K3K)		467578	A3RJ67K3K
(Highland/YUYU(5961944/2GN4E1U8)		5961944	2GN4E1U8

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Extract = Table.TransformColumns(Source, {{"Column1", each Text.BetweenDelimiters(_, "(", ")", {0, RelativePosition.FromEnd}, 0), type text}}),
    Split = Table.SplitColumn(Extract, "Column1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Column1.1", "Column1.2"})
in
    Split[/SIZE]

 

[Eric W]

Formula option:

Excel 2012

	A	B	C
1	JAMES WALKER(614726/8CZNISNS)	8CZNISNS	614726
2	ABC Ltd (306521476/3BAX80ZJ)	3BAX80ZJ	306521476
3	Woodside/Ltd (3953084/MZ0QR1LJ)	MZ0QR1LJ	3953084
4	Smiths) Ltd (467578/A3RJ67K3K)	A3RJ67K3K	467578
5	(Highland/YUYU(5961944/2GN4E1U8)	2GN4E1U8	5961944
6	(Highland/abc(ok)(123456/A23B34XX)	A23B34XX	123456

<tbody>
</tbody>

Sheet4
​

Worksheet Formulas Cell Formula B1 =TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",LEN(A1))),LEN(A1)),")","")) C1 =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,LEN(A1)-LEN(B1)-2),"(",REPT(" ",LEN(A1))),LEN(A1))) <tbody> </tbody>

<tbody>
</tbody>

 

[sbv1986]

Dear @sandy666

How can I add your code in #2 to workbook?

 

[sandy666]

copy M-code from the post
use Ctrl+T to change range to Excel Table
use From Table option

open Advanced Editor and replace code there with copied code

btw. this is NOT vba

 

[sbv1986]

@sandy666

I using office 2013 but there's not have Get & tranform. Could you give a some intrucsion

 

[sandy666]

sure, download free Power Query add-in from MS site, install and use

 

[sbv1986]

Thanks for your help, I'll try.

Have a nice day.

 

[sandy666]

you are welcome

 

[sandy666]

it seems you know nothing about Power Query so maybe it will help:

PowerQuery aka Get&Transform (links):

• What is PowerQuery
• Getting started with PQ
• PowerQuery Help
• PowerQuery M reference