Formula to get data with specific logic from a string

PANIGGR

New Member
Joined
Sep 4, 2015
Messages
13
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

Well-known Member
Joined
Oct 24, 2015
Messages
3,822
with Power Query aka Get&Transform

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

Code:
[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

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,994
Formula option:

Excel 2012
ABC
1JAMES WALKER(614726/8CZNISNS)8CZNISNS614726
2ABC Ltd (306521476/3BAX80ZJ)3BAX80ZJ306521476
3Woodside/Ltd (3953084/MZ0QR1LJ)MZ0QR1LJ3953084
4Smiths) Ltd (467578/A3RJ67K3K)A3RJ67K3K467578
5(Highland/YUYU(5961944/2GN4E1U8)2GN4E1U85961944
6(Highland/abc(ok)(123456/A23B34XX)A23B34XX123456

<tbody>
</tbody>
Sheet4


Worksheet Formulas
CellFormula
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>
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,822
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
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,822
sure, download free Power Query add-in from MS site, install and use
 
Last edited:

Forum statistics

Threads
1,082,305
Messages
5,364,406
Members
400,801
Latest member
julievandermeulen

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top