Split Alphabet only from Left Just before Numbers only in Power query

[srehman]

Hi Team,

I have used following approach but not working

Text.Select([Rawdata],{"A".."Z"}) Data(AD1022010A) Result(ADA) wrong I need (AD)
Text.Select([Rawdata],{"0".."9"})

Split text & number power query.xlsx
	D	E
1		Required Result
2	Rawdata	Starting Alphabet from left before numbers
3	AD102201A	AD
4	AIRL	AIRL
5	AP0332010B	AP
6	ARILS78	ARILS
Sheet1

 

[srehman]

Yes i have done by Post #5 if you have text like DAM001 result is DD it should be DAM ,
Bc subtract -1 , if i removed this 1 it will effect on other data.

 

[sandy666]

Rawdata		Rawdata	Text Range
AD102201A		AD102201A	AD
AIRL		AIRL	AIRL
AP0332010B		AP0332010B	AP
ARILS78		ARILS78	ARIL
DD3403A1		DD3403A1	DD
DAM001		DAM001	DAM

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    Number = Table.AddColumn(Source, "Number", each Text.Select([Rawdata],{"0".."9"})),
    LenRaw = Table.AddColumn(Number, "Length", each Text.Length([Rawdata]), Int64.Type),
    LenNum = Table.AddColumn(LenRaw, "Length.1", each Text.Length([Number]), Int64.Type),
    Subtract = Table.AddColumn(LenNum, "Subtraction", each [Length] - [Length.1], type number),
    IF = Table.AddColumn(Subtract, "IF", each if [Length.1] = 0 or [Length.1] = [Subtraction] then [Subtraction] else [Subtraction]-1),
    Range = Table.AddColumn(IF, "Text Range", each Text.Middle([Rawdata], 0, [IF]), type text),
    TSC = Table.SelectColumns(Range,{"Rawdata", "Text Range"})
in
    TSC

 

[sandy666]

I think it can be applied to all your cases

Rawdata		TR
AD102201A		AD
AIRL		AIRL
AP0332010B		AP
ARILS78		ARILS
DD3403A1		DD
DAM001		DAM
POL321A		POL
WEIRD455555DDD123		WEIRD

let
    Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
    Trim = Table.AddColumn(Source, "Trim", each Text.Trim([Rawdata], {"0".."9"})),
    Number = Table.AddColumn(Trim, "Number", each Text.Select([Trim], {"0".."9"})),
    TR = Table.AddColumn(Number, "TR", each Text.Replace([Trim],[Number], "|")),
    ETBD = Table.TransformColumns(TR, {{"TR", each Text.BeforeDelimiter(_, "|"), type text}}),
    TSC = Table.SelectColumns(ETBD,{"TR"})
in
    TSC

 

[sandy666]

another approach
this is very good fun to extracting the assumed criteria

Rawdata		Result
AD102201A		AD
AIRL		AIRL
AP0332010B		AP
ARILS78		ARILS
DD3403A1		DD
DAM001		DAM
POL321A		POL
WEIRD455555DDD123		WEIRD
ODD5QWERTY66ZXCVB9988		ODD
XYZ33WAB543ASD128SSD555		XYZ

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Number = Table.AddColumn(Source, "FC", each Text.Select([Rawdata],{"0".."9"})),
    FC = Table.TransformColumns(Number, {{"FC", each Text.Start(_, 1), type text}}),
    Pos = Table.AddColumn(FC, "Pos", each Text.PositionOf([Rawdata], [FC])),
    Range = Table.AddColumn(Pos, "Text Range", each Text.Middle([Rawdata], 0, [Pos]), type text),
    IF = Table.AddColumn(Range, "Result", each if [Pos] = 0 then [Rawdata] else [Text Range]),
    TSC = Table.SelectColumns(IF,{"Result"})
in
    TSC

 

[srehman]

Awesome approaches both are working, I promised i will not add anymore scenarios in this post.

Lot of thanks for Sandy & Rich. for your time & kind support.

The habit of sharing knowledge 100% will increase our knowledge more. This is real truth.

Take Care

 

[sandy666]

You are welcome

I promised i will not add anymore scenarios in this post

You can add more and bigger strings but before you post, test it with M from post#24 where you can see it works with longer strings
the rule for this is: start with letters and then numbers - it doesn't matter what will be after that

 

[srehman]

Understood Thanks