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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]

<colgroup><col width="97"></colgroup><tbody>
[TD="class: xl66, width: 97"]JAMES WALKER (614726/ 8CZNISNS )
[/TD]

</tbody>

[/TD]
[TD]8CZNISNS
[/TD]
[TD]614726
[/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="97"></colgroup><tbody>
[TD="class: xl66, width: 97"]ABC Ltd (306521476/ 3BAX80ZJ )
[/TD]

</tbody>

[/TD]
[TD]3BAX80ZJ
[/TD]
[TD]306521476
[/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="97"></colgroup><tbody>
[TD="class: xl66, width: 97"]Woodside/Ltd (3953084/ MZ0QR1LJ )
[/TD]

</tbody>

[/TD]
[TD]MZ0QR1LJ
[/TD]
[TD]3953084
[/TD]
[/TR]
[TR]
[TD]

<colgroup><col width="97"></colgroup><tbody>
[TD="class: xl66, width: 97"]Smiths) Ltd (467578/ A3RJ67K3K )
[/TD]

</tbody>

[/TD]
[TD]A3RJ67K3K
[/TD]
[TD]467578
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 97"]
<colgroup><col width="97"></colgroup><tbody>[TR]
[TD="class: xl66, width: 97"](Highland/YUYU(5961944/2GN4E1U8)
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]2GN4E1U8
[/TD]
[TD]5961944
[/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

with Power Query aka Get&Transform

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Column1.1[/td][td=bgcolor:#70AD47]Column1.2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]JAMES WALKER(614726/8CZNISNS)[/td][td][/td][td=bgcolor:#E2EFDA]614726[/td][td=bgcolor:#E2EFDA]8CZNISNS[/td][/tr]

[tr=bgcolor:#FFFFFF][td]ABC Ltd (306521476/3BAX80ZJ)[/td][td][/td][td]306521476[/td][td]3BAX80ZJ[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Woodside/Ltd (3953084/MZ0QR1LJ)[/td][td][/td][td=bgcolor:#E2EFDA]3953084[/td][td=bgcolor:#E2EFDA]MZ0QR1LJ[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Smiths) Ltd (467578/A3RJ67K3K)[/td][td][/td][td]467578[/td][td]A3RJ67K3K[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7](Highland/YUYU(5961944/2GN4E1U8)[/td][td][/td][td=bgcolor:#E2EFDA]5961944[/td][td=bgcolor:#E2EFDA]2GN4E1U8[/td][/tr]
[/table]

[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
JAMES WALKER(614726/8CZNISNS)	8CZNISNS
ABC Ltd (306521476/3BAX80ZJ)	3BAX80ZJ
Woodside/Ltd (3953084/MZ0QR1LJ)	MZ0QR1LJ
Smiths) Ltd (467578/A3RJ67K3K)	A3RJ67K3K
(Highland/YUYU(5961944/2GN4E1U8)	2GN4E1U8
(Highland/abc(ok)(123456/A23B34XX)	A23B34XX

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]614726[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]306521476[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]3953084[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]467578[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]5961944[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]123456[/TD]

</tbody>

Sheet4
​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B1[/TH]
[TD="align: left"]=TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",LEN(A1))),LEN(A1)),")",""))[/TD]
[/TR]
[TR]
[TH]C1[/TH]
[TD="align: left"]=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,LEN(A1)-LEN(B1)-2),"(",REPT(" ",LEN(A1))),LEN(A1)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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