Find value between 2 characters

[stroffso]

Hi All,

I have a nicely aligned workbook but there is one small part I am trying to figure out.

Our Product unit numbers go from 1-50 however they are all within a string for that product code. I know how to get figures to the left and the right etc but I want to get figures in between certain values.

Example of the strings I am using:

Levant [1 - 304554-3022]
Levant [18 - 304554-3022]
Hobson [24 - 304554-3022]

So in the above 3 cases the values returned would be
1
18
24

Simply, Anything after the [ but before the first -

All help greatly appreciated

thanks

 

[lrobbo314]

You can do this with PowerQuery.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("[", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1.2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.2.1", "Column1.2.2", "Column1.2.3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Column1.1", "Column1.2.2", "Column1.2.3"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column1.2.1", "Product Code"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Product Code", Int64.Type}})
in
    #"Changed Type"

 

[lrobbo314]

Here's a VBA solution as well. This code assumes that your data begins in A1, and it will output the results to column C.

Sub PRODUCTNUMBER()
Dim SP() As Variant: SP = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row()).Value
Dim AL As Object: Set AL = CreateObject("System.Collections.ArrayList")

For i = LBound(SP) To UBound(SP)
    AL.Add (Trim(Split(Right(SP(i, 1), Len(SP(i, 1)) - InStr(SP(i, 1), "[")), " ")(0)))
Next i

Range("C1").Resize(AL.Count, 1).Value = Application.Transpose(AL.toarray)

End Sub

 

[stroffso]

Thanks so much for that I thought there would be a way with a formula but will run it with the VBA as I am not too up on Power query but thank you both for your solutions

Here's a VBA solution as well. This code assumes that your data begins in A1, and it will output the results to column C.

Sub PRODUCTNUMBER()
Dim SP() As Variant: SP = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row()).Value
Dim AL As Object: Set AL = CreateObject("System.Collections.ArrayList")

For i = LBound(SP) To UBound(SP)
    AL.Add (Trim(Split(Right(SP(i, 1), Len(SP(i, 1)) - InStr(SP(i, 1), "[")), " ")(0)))
Next i

Range("C1").Resize(AL.Count, 1).Value = Application.Transpose(AL.toarray)

End Sub

 

[lrobbo314]

This formula seems to work too,

=TRIM(MID(A1,FIND("[",A1)+1,FIND(" ",A1,FIND("[",A1))-FIND("[",A1)))

 

[Rick Rothstein]

Here is a formula solution...

=MID(TRIM(LEFT(A1,FIND("-",A1)-1)),FIND("[",A1)+1,2)

Since your product numbers run from 1 to 50 (max of 2 digits), I was able to use that fact in the MID function.

By the way, we can use this formula as the basis of a VBA solution...

[table="width: 500"]
[tr]
	[td]Sub GetNumber()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Range("C1:C" & LastRow) = Evaluate(Replace("IF({1},MID(TRIM(LEFT(A1:A#,FIND(""-"",A1:A#)-1)),FIND(""["",A1:A#)+1,2))", "#", LastRow))
End Sub[/td]
[/tr]
[/table]

 

[Michael M]

Also maybe a formula

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][/tr]
[tr][td]

1​

[/td][td]Levant [1 - 304554-3022][/td][td]=MID(LEFT(A1,FIND(" -",A1)-1),FIND("[",A1)+1,LEN(A1))[/td][/tr]

[tr][td]

2​

[/td][td]Levant [18 - 304554-3022][/td][td]=MID(LEFT(A2,FIND(" -",A2)-1),FIND("[",A2)+1,LEN(A2))[/td][/tr]

[tr][td]

3​

[/td][td]Hobson [24 - 304554-3022][/td][td]=MID(LEFT(A3,FIND(" -",A3)-1),FIND("[",A3)+1,LEN(A3))[/td][/tr]
[/table]

 

[sandy666]

@post #2
enough one line with:

Table.TransformColumns(Source, {{"raw", each Text.BetweenDelimiters(_, "[", " "), type text}})

 

[jtakw]

Hi,

Another formula solution, if the numbers are Always 2 digits (i.e. between 1 to 50), why can't it be just:

Book1
	A	B	C
1	Levant [1 - 304554-3022]	1	1
2	Levant [18 - 304554-3022]	18	18
3	Hobson [24 - 304554-3022]	24	24
Sheet674

Cell Formulas
Range	Formula
B1	=MID(A1,FIND("[",A1)+1,2)+0
C1	=TRIM(MID(A1,FIND("[",A1)+1,2))

Formulas copied down.
B1 formula converts results to Real Numbers.
C1 formula results as Text.

 

[Rick Rothstein]

...if the numbers are Always 2 digits (i.e. between 1 to 50), why can't it be just:

Book1
	A	B	C
1	Levant [1 - 304554-3022]	1	1
2	Levant [18 - 304554-3022]	18	18
3	Hobson [24 - 304554-3022]	24	24
Sheet674

Cell Formulas
Range	Formula
B1	=MID(A1,FIND("[",A1)+1,2)+0
C1	=TRIM(MID(A1,FIND("[",A1)+1,2))

But of course...