Forum Discussion
Complex selection from excel to PBI
Greetings! I am trying to migrate the following excel formula to PBI and am meeting some challenges. Would someone lone a set of extra eyes to this to see where I might be able to improve...(dramatically) this selection efferot. Yes, I know I should just get better data, but that is not an option at this time. TIA
=IF(IFERROR(VALUE(MID([Record Name],7,SEARCH(" - ",[Record Name],1)+3,SEARCH(" - ",[RECORD NAME],SEARCH(" - ",[RECORD NAME],1)+1)-SEARCH(" - ",[RECORD NAME],1)-2)),"")="", LEFT(TRIM(RIGHT([RECORD NAME],LEN([RECORD NAME]) - SEARCH("-", [RECORD NAME], SEARCH("-", [RECORD NAME]) + 1))), SEARCH("-",TRIM(RIGHT([RECORD NAME],LEN([RECORD NAME]) - SEARCH("-", [RECORD NAME], SEARCH("-", [RECORD NAME]) + 1))),1)-1), IFERROR(VALUE(MID([RECORD NAME],SEARCH(" - ",[RECORD NAME],1)+3,SEARCH(" - ",[RECORD NAME],SEARCH(" - ",[RECORD NAME],1)+1)-SEARCH(" - ",[RECORD NAME],1)-2)),""))
2 Replies
mdphillikp - can you provide sample data with expected results?
Does this actually work in Excel? MID ( ) only seems to take 3 arguments where your formula is passing 5 in the first MID ( ).
GROUP - SPECIAL EQUESTRIAN SERVICES, Inc., - 10969 - REP - 9/2019
City of Charleston Parks and Rec - 10245 - 300,301,302,303 - CH - 2018
GROUP - TriMAPS III - 10399 (JUP) - 10399 - QEN - 8/2019
Buccaroo Brokerage-Wurray-Wheyland Sales - 16423 - P00,100 - TR - 2019
The desired field is the "10969, 10245, 10399, 16423". The target is always a 5 digit number buried in the text.
