Substituting CRLF with "+" - Transforming a cell containing numbersin a sum of its numbers

[JJ]

Hi,

I have a 1000 row table in excel with a field that has several numbers in the same cell, like:

3
451
10

I need to add these numbers together so I was trying to transform this into a formula: Adding a "+" sign in the beggining is trivial bu how can I put a "+" i n front of 451 and 10 so that I can transform this field in a sum of its numbers?

for instance: I think between 3 and 451 there is a CRLF. Is it possible to find/replace this char by a "+" sign? Other ways?

Thanks/Brgds
joao

 

[Claus Busch]

Hi,

Am Tue, 26 Nov 2013 06:00:59 -0800 (PST) schrieb JJ:

3
451
10

I need to add these numbers together so I was trying to transform this into a formula: Adding a "+" sign in the beggining is trivial bu how can I put a "+" i n front of 451 and 10 so that I can transform this field in a sum of its numbers?

your values in column A (or modify the range to suit):

Sub Test()
Dim LRow As Long
Dim rngC As Range

LRow = Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In Range("A1:A" & LRow)
rngC = "=" & Replace(rngC, Chr(10), "+")
Next
With Range("A1:A" & LRow)
.WrapText = False
.EntireRow.AutoFit
End With
End Sub


Regards
Claus B.

 

[GS]

Hi,

I have a 1000 row table in excel with a field that has several
numbers in the same cell, like:

3
451
10

I need to add these numbers together so I was trying to transform
this into a formula: Adding a "+" sign in the beggining is trivial bu
how can I put a "+" i n front of 451 and 10 so that I can transform
this field in a sum of its numbers?

for instance: I think between 3 and 451 there is a CRLF. Is it
possible to find/replace this char by a "+" sign? Other ways?

Thanks/Brgds
joao

What you need here is to impliment 'formula-style' input so you have
both a record of each value as well as the total displayed...

=3+451+10

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion