DAX Formula help

Nizamrcl

New Member
Joined
Sep 16, 2015
Messages
5
Hi,

I am looking for a formula of DAX to find a specific number in invoice column

Eg: Invoice number always start with number 2 and it has 8 digits as shown in below table (highlighted)

KB335/21635956/A-AMOUNT-R091018-R171018

<tbody>
</tbody>
21600589/A-2 (INVOICE VALUE EXCEEDS ORDER VALUE)

<tbody>
</tbody>
KB335/21600589/A-AMOUNT MISMATCH

<tbody>
</tbody>
21709491/B

<tbody>
</tbody>
KB335/21718253

<tbody>
</tbody>
21683908/B (ORDER NOT AVAILABLE)

<tbody>
</tbody>
8B918/21670664

<tbody>
</tbody>
8B918/21705980/B-1

<tbody>
</tbody>
8B918/21721500

<tbody>
</tbody>
21722557

<tbody>
</tbody>
8B918/21722328

<tbody>
</tbody>
FAPR1/21661374/A R030319 -Amount mismatch

<tbody>
</tbody>
X1360/21621259

<tbody>
</tbody>

<tbody>
</tbody>
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Hi,

I had stored this mixed data in the "Table2" with column name "Mix". Below DAX formula should work for you (This is similar in Excel formula too)

Code:
=IFERROR(MID(Table2[Mix],SEARCH("/2",Table2[Mix])+1,8),LEFT(Table2[Mix],8))


Book1
AB
1MixInvoice
22172255721722557
321600589/A-2 (INVOICE VALUE EXCEEDS ORDER VALUE)21600589
421683908/B (ORDER NOT AVAILABLE)21683908
521709491/B21709491
68B918/2167066421670664
78B918/21705980/B-121705980
88B918/2172150021721500
98B918/2172232821722328
10FAPR1/21661374/A R030319 -Amount mismatch21661374
11KB335/21600589/A-AMOUNT MISMATCH21600589
12KB335/21635956/A-AMOUNT-R091018-R17101821635956
13KB335/2171825321718253
14X1360/2162125921621259
Sheet3
Cell Formulas
RangeFormula
B2=IFERROR(MID(A2,SEARCH("/2",A2)+1,8),LEFT(A2,8))
 
Upvote 0
ooops

incase if the line has blanks (other than /2) then this is not working

eg:
X1594 21209478 G4322094/096/097

<tbody>
</tbody>
X1360R 21342611

Kindly help

<tbody>
</tbody>
 
Upvote 0
If there are no additional variants, import Table2 (Column Mix) into the Power Query and in the Advanced Editor paste the following code:

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Mix", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Mix", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), {"Mix.1", "Mix.2", "Mix.3"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Mix.1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Mix.1.1", "Mix.1.2", "Mix.1.3"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter1", "AllFirst2", each if Text.Length([Mix.1.1])=8 and Text.Start([Mix.1.1],1)="2" then [Mix.1.1] else if Text.Length([Mix.1.2])=8 and Text.Start([Mix.1.2],1)="2" then [Mix.1.2] else [Mix.2])
in
    #"Added Custom"

New Column "AllFirst2" load in Data Model
 
Last edited:
Upvote 0
Hi Nizamrcl,

If that is the only other case and you don't want to use Power Query which is a great solution too, a quick change to the formula below will work:

Code:
=IFERROR(IFERROR(MID(Table2[Mix],SEARCH("/2",Table2[Mix])+1,8),MID(Table2[Mix],SEARCH(" 2",Table2[Mix])+1,8)),LEFT(Table2[Mix],8))

ooops

incase if the line has blanks (other than /2) then this is not working

eg:
X1594 21209478 G4322094/096/097

<tbody>
</tbody>
X1360R 21342611

Kindly help

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top