Extract Data from String

[mm44]

I have a gallery that contains a field showing the location of a item.  Examples of the locations are:

A1A

A1B

A10A

A10B

AA1A

I would like to break these locations down by Row, Bay and Bin to display in individual labels.  The row is indicated by the first characters of the string until the first digit.  Bay is identified by the middle digits surrounded by characters.  Bin is identify by the last character values after the last digit.  Examples of this are below:

Row A. Bay 1. Bin A

Row A. Bay 1. Bin B

Row A. Bay 10. Bin A

Row A. Bay 10. Bin B

Row AA. Bay 1. Bin A

Can someone please provide a method of breaking down a string into each of these values?

Thank you in advance for your assistance!

[WarrenBelz]

Hi @mm44 ,

I had a go at this for a while and the problem is the sheer number of variations and places the characters can be. You can get the Row with this

"Row " & Left(
    YourString,
    If(
        IsNumeric(
            Mid(
                YourString,
                2,
                1
            )
        ),
        1,
        2
    )
)

but then have four combinations to test the Bay character places (2, 2&3, 3 and 3&4) to see if it is a number and each one of them has different options for the Bin depending on how many characters remain.

You really need a delimiter here (A-1-A AA-10-B etc) and then simply use Split().

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[Drrickryp]

@mm44 

It would be easier to implement @WarrenBelz's suggestion by using Power Query to reshape your data into 3 columns. 

[EddieE]

@mm44 

Try this

With(

  {
     wBay: Find( Match( yourStr, Digit) ),

     wBin: Len( yourStr) - 1

  },
  Concatenate(

       ”Row “ & Left( yourStr, wBay - 1) &

       “. Bay “ & Mid( yourStr, wBay, wBin) &

       “. Bin “ & Right( yourStr, 1 )

   )

)

Sorry, on my phone so cannot test this but think it’ll work?

[WarrenBelz]

Hi @EddieE ,

I played with Match(Digit) as well (I did no better) - this bit in your code is not valid

wBay: Find(Match(yourStr.Text,Digit)),

Also the length of this (one or two places) determines the starting point of Bin which can be either one or two places.

[EddieE]

@WarrenBelz 

My bad, Should be

Find( yourStr, Match( yourStr, Digit) )

Also, I took this

’… Bin is identify by the last character …’ 

to mean there was only one character for Bin, not 1-many?

If many, you’d just need a couple more With variables to grab that string start, eg

wStr2: Right( yourStr, Len( yourStr) - wBay),

wBinStart: Find( yourStr, Match( wStr2, Letter) )

The Bin string from my original code would then become

Right( yourStr, Len( yourStr) - wBinStart)

This all needs some refactoring and tidying up but should work 

[WarrenBelz]

@EddieE 

Still does not work . . . yourStr is a Text Variable as per post

[WarrenBelz]

@mm44 ,

Probably over complex, but I had a go using code I knew would work

With(
   {
      wRow: 
      If(
         IsNumeric(
            Mid(
               yourStr,
               2,
               1
            )
         ),
         1,
         2
      )
   },
   With(
      {
         wBay: 
         If(
            IsNumeric(
               Mid(
                  yourStr,
                  wRow + 1,
                  2
               )
            ),
            2,
            1
         )
      },
      With(
         {
            wBin: 
            With(
               {
                  wLast: 
                  Left(
                     Right(
                        yourStr,
                        2
                     ),
                     1
                  )
               },
               Right(
                  yourStr,
                  If(
                     IsNumeric(wLast),
                     1,
                     2
                  )
               )
            )
         },
         "Row " & 
         Left(
            yourStr,
            wRow
         ) & 
         ", Bay:" & 
         Mid(
            yourStr,
            wRow + 1,
            wBay
         ) & 
         ", Bin:" & wBin
      )
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

[EddieE · Accepted solution]

@WarrenBelz 

Finally turned on the laptop ...

This works:

With(
    {
        wBay: Find(Match( yourStr, Digit).FullMatch,yourStr )
    },
    With(
        {
            wBinStart: Find(Match( yourStr, Letter).FullMatch, yourStr, wBay)
        },
            Concatenate(
                "Row " & Left(yourStr, wBay - 1) & 
                ". Bay " & Mid(yourStr, wBay, wBinStart - wBay) & 
                ". Bin " & Right(yourStr, Len(yourStr) - wBinStart + 1)
        )
    )
)

@mm44 

My code assumes that your format is always [A-Z][0-9][A-Z] and not any other format.

[mm44]

@EddieE - this is just what I needed.  Thank you very much for the help!