Formula to extract only numbers from the end of a string until occurrence of alphabet or /

rmghosh 

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:

1. Create a query on the data you want to split the number from
2. Create a new column that reverses the data you want to split
3. Split the new column on digit to non-digit, keeping only the first split item
4. Reverse the item obtained in step 3
5. Split step 4 on digit to non-digit, again keeping only the first item
6. Change the data type of the result of Step 5 to whole number
7. Optionally rename the output column

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.