Split cell, extract numbers, get difference

[Siddozz]

Hi all,

I’m looking for a formula where in i could separate numbers from a cell containing text and numbers and also find the difference between those 2 numbers.

For eg: Runs scored by Mike 560 vs Richard 345
This is the actual text which i have a in a single cell however only need the difference between the numbers. Need to apply this to a column.

Any help would be appreciated.

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Character Transition" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column1.1", "Column1.2", "Column1.3"}),
    #"Split Column by Character Transition1" = Table.SplitColumn(#"Split Column by Character Transition", "Column1.2", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Column1.2.1", "Column1.2.2"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Character Transition1",{"Column1.2.1", "Column1.3"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Column1.2.1", Int64.Type}, {"Column1.3", Int64.Type}}),
    #"Inserted Subtraction" = Table.AddColumn(#"Changed Type", "Subtraction", each [Column1.2.1] - [Column1.3], Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Subtraction",{"Column1.2.1", "Column1.3"})
in
    #"Removed Columns"

Book6
	A	B	C
2	Runs scored by Mike 560 vs Richard 345		215
Sheet1

 

[Rick Rothstein]

For eg: Runs scored by Mike 560 vs Richard 345

Is this the exact form for all cells... a number before " vs " and a number at the end? Also, will the smaller number always be number on the end of the text?

 

[Siddozz]

Is this the exact form for all cells... a number before " vs " and a number at the end? Also, will the smaller number always be number on the end of the text?

Yes this is the exact form for all cells and numbers may differ first one could be smaller or the second one

 

[Rick Rothstein]

This formula should work...

=ABS(TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" vs",A1)-1)," ",REPT(" ",99)),99))-TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))

IF the name between the "vs" and the last number is ALWAYS a single name (never more), than you can use this shorter formula...

=SUM(LEFT(RIGHT(SUBSTITUTE(A1," ",REPT(" ",200)),{4,1}*200),200)*{1,-1})

 

[Peter_SSs]

This formula should work...

=ABS(TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" vs",A1)-1)," ",REPT(" ",99)),99))-TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)))

That could be trimmed (or perhaps it should be untrimmed).
Also, although calculation time will probably not be an issue here, since we are looking for numbers they could not possibly be anything like 99 characters long so we could save the time in joining so many space characters together. I don't think anybody would score 999,999,999 runs so 9 should be more than enough.

=ABS(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" vs",A1)-1)," ",REPT(" ",9)),9)-RIGHT(SUBSTITUTE(A1," ",REPT(" ",9)),9))