Jul 15 2020 05:25 AM - edited Jul 15 2020 06:25 AM
Hi All
I am new to Excel. I want to build a formula on Excel which extracts only the numerical values from the end of the string until the occurrence of an alphabet or special character.
For example.
Column A is the entry and Column B should be my result as I need this measure to join two files.
A B
ABC 132/4X32 32
US,9/24FZ,XYZ/ABC WIP 24
QWERT 4/2 x 24FZ AB AB 24
ABC UYI 9/24FZ 24
ABC UIOP 10ML/9 9
ABC YUIO 12/8Z 8
Please let me know if there is a formula to extract such a level of information.
Any help would be appreciated. Thank you in advance.
Jul 15 2020 06:13 AM
Are numbers you trying to extract always two digit numbers?
Can you provide more such samples with the desired output?
Jul 15 2020 06:22 AM
Solution
What you're trying to do is not so easy to do with an Excel formula, but it is somewhat trivial to do in Power Query.
The process summary is as follows:
Step 1
Put your cursor anywhere in your list of data to transform and use Data>Get & Transform Data>From Table/Range. This will create a query on that dataset and open the Power Query Editor.
Step 2
Use Add Column>Custom Column with this formula (assuming your list of strings is in a column called data):
Text.Reverse([data])
Call the column "Reversed"
Step 3
Use Transform>Split Column>By Digit to Non-digit
This will create a number of columns called Reversed.1, Reversed.2, etc.
Select all of the created columns except the first one and right-click then Remove those columns.
At this point, my working example looks like this:
Step 4
Again, use Add Column>Custom Column and reverse the right-most column shown above, with this formula:
Text.Reverse([Reversed.1])
Call this column "Reversed2".
Step 5
Select Reversed2 then use Transform>Split By>Digit to Non-digit
At this point, you will have this:
You can see that you have the right data in the column Reversed2.1 above. Now a little cleanup.
Remove the columns you don't need by right-click remove.
Step 6
Click the ABC icon at the top of the Reversed2.1 column shown above and change the data type to Whole Number.
Step 7
Right click the Reversed2.1 column and Rename it to something more sensible. I renamed mine to "LastNumber". You can now use Home>Close&Load to put the data back into the workbook.
For reference, here's the entire query from Home>Advanced Editor:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"data", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Reversed", each Text.Reverse([data])),
#"Split Column by Character Transition" = Table.SplitColumn(#"Added Custom", "Reversed", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Reversed.1"}),
#"Added Custom1" = Table.AddColumn(#"Split Column by Character Transition", "Reversed2", each Text.Reverse([Reversed.1])),
#"Split Column by Character Transition1" = Table.SplitColumn(#"Added Custom1", "Reversed2", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Reversed2.1", "Reversed2.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition1",{"Reversed2.2", "Reversed.1"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Reversed2.1", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Reversed2.1", "LastNumber"}})
in
#"Renamed Columns"
My example workbook is attached here.
Jul 15 2020 06:27 AM
No, it could also be one digit!
I updated some more examples for your reference.
Thank you.
Jul 15 2020 06:40 AM
Maybe these two formulas will help you.
Copied freehand from the Internet :)
I would be happy to find out if I could help.
Nikolino
I know I don't know anything (Socrates)
Jul 15 2020 06:44 AM
Jul 15 2020 06:22 AM
Solution
What you're trying to do is not so easy to do with an Excel formula, but it is somewhat trivial to do in Power Query.
The process summary is as follows:
Step 1
Put your cursor anywhere in your list of data to transform and use Data>Get & Transform Data>From Table/Range. This will create a query on that dataset and open the Power Query Editor.
Step 2
Use Add Column>Custom Column with this formula (assuming your list of strings is in a column called data):
Text.Reverse([data])
Call the column "Reversed"
Step 3
Use Transform>Split Column>By Digit to Non-digit
This will create a number of columns called Reversed.1, Reversed.2, etc.
Select all of the created columns except the first one and right-click then Remove those columns.
At this point, my working example looks like this:
Step 4
Again, use Add Column>Custom Column and reverse the right-most column shown above, with this formula:
Text.Reverse([Reversed.1])
Call this column "Reversed2".
Step 5
Select Reversed2 then use Transform>Split By>Digit to Non-digit
At this point, you will have this:
You can see that you have the right data in the column Reversed2.1 above. Now a little cleanup.
Remove the columns you don't need by right-click remove.
Step 6
Click the ABC icon at the top of the Reversed2.1 column shown above and change the data type to Whole Number.
Step 7
Right click the Reversed2.1 column and Rename it to something more sensible. I renamed mine to "LastNumber". You can now use Home>Close&Load to put the data back into the workbook.
For reference, here's the entire query from Home>Advanced Editor:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"data", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Reversed", each Text.Reverse([data])),
#"Split Column by Character Transition" = Table.SplitColumn(#"Added Custom", "Reversed", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Reversed.1"}),
#"Added Custom1" = Table.AddColumn(#"Split Column by Character Transition", "Reversed2", each Text.Reverse([Reversed.1])),
#"Split Column by Character Transition1" = Table.SplitColumn(#"Added Custom1", "Reversed2", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Reversed2.1", "Reversed2.2"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition1",{"Reversed2.2", "Reversed.1"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Reversed2.1", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Reversed2.1", "LastNumber"}})
in
#"Renamed Columns"
My example workbook is attached here.