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

 

[sandy666]

Rawdata	TBD
AD102201A	AD
AIRL	AIRL
AP0332010B	AP
ARILS78	ARILS

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TS = Table.AddColumn(Source, "Number", each Text.Select([Rawdata],{"0".."9"})),
    TBD = Table.AddColumn(TS, "TBD", each try Text.BeforeDelimiter([Rawdata], [Number]) otherwise [Rawdata], type text),
    RC = Table.RemoveColumns(TBD,{"Number"})
in
    RC

 

[srehman]

Perfect Thanks

 

[sandy666]

You are welcome

btw. did you try this for all percent columns at once?

 

[srehman]

One Problem here

Split text & number power query.xlsx
	F	G
11	Rawdata	TBD
12	AD102201A	AD
13	AIRL	AIRL
14	AP0332010B	AP
15	ARILS78	ARILS
16	DD3403A1	DD3403A1
Sheet1

 

[sandy666]

are you sure example from post #5 is representative?

 

[sandy666]

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

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[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 then [Length] 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

 

[srehman]

this is now with more data sorry i could't add in my prev post

 

[sandy666]

sure

 

[srehman]

Thanks Perfect Working Rich, & Sandy your solution is perfect as well according to my post.

You Guys are gr8.
Take Care