Power Query start at second instance of delimiter

B

btwice

New Member
Joined
Dec 16, 2014
Messages
17
Office Version
  1. 365
Platform
  1. Windows
I'm trying to pull a set of numbers after a delimiter using "_1" as a delimiter, but in some values in the data this delimiter shows up one time before the one that I want to pull from. How would I use an if statement to do something like below:

if delimiter("_1") > 0 then start at delimiter("_1") position 1 (second instance) else start at delimiter("_1") position 0 (first instance)

Appreciate any help, thanks.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
Sure, see below. Notice rows 4-6 can't pull the "Notification" out as it sees WEIMAR_1101, and I'm using _1 as a delimiter.

Book1
ABCDEFGHIJ
1VendorNameDate modifiedDate createdFolder PathOrderNotificationPackage typeOrder_VendorOrder_Notification
2VendorNB_town_45564190_118977199_vendor_KAA_RFP_22Mar24453734537345564190118977199RFP_45564190_Edison45564190_118977199
3VendorNB_town_45564215_118978175_vendor_KAA_RFP_22Mar24453734537345564215118978175RFP_45564215_Edison45564215_118978175
4VendorSI_WEIMAR_1101_31686873_124052853_2AA_Contractor_ConPkgComp453734537331686873ConP31686873_MEGA
5VendorSI_WEIMAR_1101_31686736_126433769_2AA_Contractor_PreFieldPkg453734537331686736PreF31686736_MEGA
6VendorSI_WEIMAR_1101_31686437_121497058_2AA_Contractor_PreFieldPkg453734537331686437PreF31686437_MEGA
Sheet1
 
Upvote 0
I believe you are trying to extract the 9 digits. I just recently answered a question on this website here.

You can add a Power Query column that extracts these 9 digits:

Power Query: 
List.Select(Text.Split(
    Text.Select([Name],{"0".."9","_"}),"_"),(x)
    =>Text.Length(x)=9)


Got this from here.
 
Last edited:
Upvote 0
Solution
Yes this works great it looks like, your answer to my other question probably works as well but I used a different method to get the result I needed. Appreciate the help!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Power Query refresh taking HOURS after adding custom column
Replies
3
Views
546
Matt Allington
M
R
  • Question
Line break causing issues combining .csv file with comma delimiter
Replies
1
Views
1K
Muhammad_Usman
Muhammad_Usman
T
  • Question
Help converting Power Query that imports data to VBA
Replies
0
Views
379
The Shy Retirer
T
jlhoover3
  • Solved
Power Query - Filter table column based on cell value
Replies
3
Views
201
alansidman
A
JackDanIce
  • Question
Reading csv PowerQuery [Source = try ... otherwise ... ] fails, paths are valid
Replies
2
Views
897
JackDanIce
JackDanIce

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

Share this page

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