Extract only sets of numbers from a text and number cell

[empach]

Hi,

I'm struggling with finding any formula to extract sets of numbers from a cell.

In cell C4 I have the following data "Electric 34, Gas 28, Water 5" (note the commas - are these necessary of would they hinder any formula)? I would like a formula in D4 to sum just the numbers from C4 ie "34+28+5" This would then sum the numbers to "67"

The column C is all the dates of the year and the information (bills I'm paying from my bank account) will change on each row so I can't use the LEFT, MID, RIGHT formulas as this will change for each row and sometimes there won't be any text in column C.

Is there a way? Thanks in advance

P

 

[Jeffrey Mahoney]

Hi Empach. Please try this:
This function can be called from within a cell and used like this. =SumNumbers(A1)
Where A1 is the cell containing the comma separated text with numbers.

Function SumNumbers(Rng As Range) As Double
  Dim A As String, B As String, C As String
  Dim Arr As Variant
  Dim fn As Long
  Dim X As Long, Y As Long
  Dim Sm As Double
 
  A = Rng.Value
  Arr = Split(A, ",")
 
  For X = 0 To UBound(Arr)
    C = ""
    For Y = 1 To Len(Arr(X))
      B = Mid(Arr(X), Y, 1)
      If B Like "#" Then C = C & B
    Next Y
    Sm = Sm + Val(C)
  Next X
  SumNumbers = Sm
 
End Function

 

[alansidman]

An alternative if you are willing to use Power Query. Here is the Mcode. Look in my signature block for Power Query Tutorial

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", Int64.Type}, {"Column1.3", type text}, {"Column1.4", Int64.Type}, {"Column1.5", type text}, {"Column1.6", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [Column1.2] + [Column1.4] + [Column1.6])
in
    #"Added Custom"

 

[empach]

Hi Empach. Please try this:
This function can be called from within a cell and used like this. =SumNumbers(A1)
Where A1 is the cell containing the comma separated text with numbers.

Function SumNumbers(Rng As Range) As Double
  Dim A As String, B As String, C As String
  Dim Arr As Variant
  Dim fn As Long
  Dim X As Long, Y As Long
  Dim Sm As Double

  A = Rng.Value
  Arr = Split(A, ",")

  For X = 0 To UBound(Arr)
    C = ""
    For Y = 1 To Len(Arr(X))
      B = Mid(Arr(X), Y, 1)
      If B Like "#" Then C = C & B
    Next Y
    Sm = Sm + Val(C)
  Next X
  SumNumbers = Sm

End Function

An alternative if you are willing to use Power Query. Here is the Mcode. Look in my signature block for Power Query Tutorial

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", Int64.Type}, {"Column1.3", type text}, {"Column1.4", Int64.Type}, {"Column1.5", type text}, {"Column1.6", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [Column1.2] + [Column1.4] + [Column1.6])
in
    #"Added Custom"

Thank you Jeffrey! This works but only for whole numbers... if I type, say, 5.85 as the water, I end up with a sum of 647!!??

An alternative if you are willing to use Power Query. Here is the Mcode. Look in my signature block for Power Query Tutorial

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", Int64.Type}, {"Column1.3", type text}, {"Column1.4", Int64.Type}, {"Column1.5", type text}, {"Column1.6", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each [Column1.2] + [Column1.4] + [Column1.6])
in
    #"Added Custom"

Thank you Alan! However I cannot get this to work - it will be my lack of understanding of your formula and VBA, so please don't feel I'm ungrateful for your efforts! And, to be honest, after watching the video and not having the same excel the power query thing frightens me!!!

 

[Akuini]

Try this UDF (User Defined Function):

Function toSum(c As Range) As Double
Dim x
    For Each x In Split(c, " ")
        toSum = toSum + Val(x)
    Next

End Function

 

[alansidman]

And, to be honest, after watching the video and not having the same excel the power query thing frightens me!!!

To take away any fears, I urge you to buy the book " M is for (Data) Monkey". It is by Ken Puls and Miguel Escobar. It will allay any fears and make you a more powerful Excel user. Also, it is on the Mr E recommendation that I purchased it.

 

[Jeffrey Mahoney]

Replace the similar line with this:
If B Like "[*0-9*.*]" Then C = C & B

Although it looks like Akuini has come up with a simple solution

 

[bosco_yip]

Or, try this formula solution

1] A1 : Electric 34, Gas 28, Water 5

2] A2 : Electric 34, Gas 28, Water 5.85

3] B1, formula copied down :

=SUMPRODUCT(--TEXT(RIGHT(TEXT(MID(SUBSTITUTE(A1&",",",",REPT(" ",16)),ROW($1:$99),17),),16),"[<>];;;\0"))

4] B1 will return 67

and

B2 will return 67.85

Regards
Bosco

 

[empach]

Try this UDF (User Defined Function):

Function toSum(c As Range) As Double
Dim x
    For Each x In Split(c, " ")
        toSum = toSum + Val(x)
    Next

End Function

BOOM!!! Perfect!!! Thank you! Works a treat!

 

[empach]

Or, try this formula solution

1] A1 : Electric 34, Gas 28, Water 5

2] A2 : Electric 34, Gas 28, Water 5.85

3] B1, formula copied down :

=SUMPRODUCT(--TEXT(RIGHT(TEXT(MID(SUBSTITUTE(A1&",",",",REPT(" ",16)),ROW($1:$99),17),),16),"[<>];;;\0"))

4] B1 will return 67

and

B2 will return 67.85

Regards
Bosco

And this also works!! Thank you!! It blows my mind trying to understand it but one day I'm sure I will!!