Today we are going to discuss about numerous methods in Excel and Power Query to extract numbers from alphanumeric string into single cell with comma as delimiter.
Lot to learn, let's dive in.
Method-1:
Formula in C3 cell,
=SUBSTITUTE(TRIM(CONCAT(IFERROR(0+MID(B3,ROW($1:$99),1)," ")))," ",", ")
Let me start from scratch,
=ROW($1:$99)
Here, we assume that maximum permissible length of alphanumeric string is 99.
We can use number up to 255 which is the maximum length, excel formula can handle.
255 is kind of overkill and so we use 99.
ROW function will generate sequence of numbers from 1 to 99.
=MID(B3,ROW($1:$99),1)
MID function helps us to extract subtext from main text based on start number and number of characters arguments.
Here, Main text is B3 cell value,
Start_num is sequence of numbers from 1 to 99,
num_char is 1.
So, MID function extract one character from each starting position from 1 to 99.
If we evaluate this part,
={"A";"B";"C";"1";"1";"D";"E";"3";"4";"5";"-";"F";"G";"6";"H";"7";"8";"9";"";"";"";"";"";"";"";""}
This is partial output from derived result. These blanks will extend up to 99 elements.
If we add zero with above array,
Texts will throw an error,
Numbers in Text format will get convert into numbers as shown below,
=#VALUE!;#VALUE!;#VALUE!;1;1;#VALUE!;#VALUE!;3;4;5;#VALUE!;#VALUE!;6;#VALUE!;7;8;9;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
IFERROR function will help us to replace errors with Space character,
{" ";" ";" ";1;1;" ";" ";3;4;5;" ";" ";" ";6;" ";7;8;9;" ";" ";" ";" ";" ";" ";" ";" ";" "}
CONCAT will take care of concatenation,
=" 11 345 6 789 "
We can use TRIM function to trim leading and trailing spaces, and this will keep only one space between words.
="11 345 6 789"
Now, we can simply substitute space character with ", " using SUBSTITUTE function.
That's it, drag the formula down.
Method-2:
=MAP(B3:B6,LAMBDA(x,TEXTJOIN(", ",,TEXTSPLIT(x,TEXTSPLIT(x,SEQUENCE(10,,0),,1),,1))))
=SEQUENCE(10,,0)
This will create sequence of numbers from 0 to 9.
col_delimiter and row_delimiter arguments in TEXTSPLIT function can handle multiple delimiters and this function has the capability to split texts at each occurrence of delimiter.
Inner TEXTSPLIT function delivers below result,
={"ABC","","DE","","","-FG","H","","",""}
1 in ignore empty argument takes care of empty strings, output will look like below,
={"ABC","DE","-FG","H"}
If we use above result as delimiters in outer TEXTSPLIT function, then result will look like,
{"11","345","6","789"}
That's it, now TEXTJOIN function will help us to concatenate these values in single cell with desired delimiter.
Method-3:
=SUBSTITUTE(TRIM(DROP(TEXTSPLIT(TRIM(CONCAT(IFERROR(0+MID(B3:B6&" "&9999999999,SEQUENCE(,99),1)," "))),,9999999999),-1))," ",", ")
This is bit longer, but concept is same as method-1,
This is single cell formula, so no need to drag the formula down.
Method-4:
Power Query Solution,
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Result = Table.AddColumn( Source , "Result", (f) => Text.Combine(
List.RemoveMatchingItems( Text.Split(Text.Combine(List.Transform(
Text.ToList(f[Input]), each Text.From( try Number.From(_)
otherwise "|"))), "|"), {""}),", "))
in
Result
This is M-Code for you, no need to panic. I can guide you through each step of transformation.
Table. AddColumn is the function used to add column,
Name of the column is 'Result',
(f) => this is the replacement of custom function each, and the function to be carried out in each row of Result column is,
Text. ToList helps us to split text into separate characters. (Just like MID in method-1)
and this function will deliver list.
List. Transform is the function used to do any transformation in list, here we replace text characters with ("|") pipe symbol and keep the numbers as it is.
Text. Combine function combines the list and delivers text.
Text. Split function splits text based on the delimiter "|" and delivers list.
List. RemoveMatchingItems will help us to skip blanks in list.
Again Text. Combine does the concatenation and delivers our desired result.
These transformation steps carried out in each row.
Comments