SOLVED

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

Copper Contributor

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.

5 Replies

@rmghosh 

Are numbers you trying to extract always two digit numbers?

Can you provide more such samples with the desired output?

best response confirmed by rmghosh (Copper Contributor)
Solution

@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:

SplitReverse1.png

 

 

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:

SplitReverse2.png

 

 

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.

No, it could also be one digit!

I updated some more examples for your reference.

Thank you.

@rmghosh 

 

Maybe these two formulas will help you.

Zahle von Text TrennenZahle von Text Trennen

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)

This worked perfectly. Thank you so much!
1 best response

Accepted Solutions
best response confirmed by rmghosh (Copper Contributor)
Solution

@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:

SplitReverse1.png

 

 

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:

SplitReverse2.png

 

 

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.

View solution in original post