Force text cell as numeric???

[dpb]

Almost got it!!!

Everything working in putting the formulae in the right locations and
works as desired if the column is numeric.

The hiccup is the column that uses the Alt+Enter trick to wrap awards on
successive lines in a cell has to be text so when write the summation
formula for the group in the summary row for the particular fund it is
interpreted as the text string of the sum.

Can manually edit the string and then resave and Excel will then convert
the cell and the sum is computed -- so the Q? now is,

How to automate that process to not have to do that every time update
the sheet?

The formula is, something like

=SUM(N14:N16)

where each of N14 thru N16 are

=sumSplitData(N14)

etc., which is the routine you helped with earlier to split the cell
data by row and total...

--

[Claus Busch]

Hi,

Am Thu, 17 Sep 2020 12:01:51 -0500 schrieb dpb:

> Almost got it!!!
>
> Everything working in putting the formulae in the right locations and
> works as desired if the column is numeric.
>
> The hiccup is the column that uses the Alt+Enter trick to wrap awards on
> successive lines in a cell has to be text so when write the summation
> formula for the group in the summary row for the particular fund it is
> interpreted as the text string of the sum.
>
> Can manually edit the string and then resave and Excel will then convert
> the cell and the sum is computed -- so the Q? now is,
>
> How to automate that process to not have to do that every time update
> the sheet?
>
> The formula is, something like
>
> =SUM(N14:N16)
>
> where each of N14 thru N16 are
>
> =sumSplitData(N14)

I have to guess because I don't have your data and your function.
Try it in the cell with:
=--(sumSplitData(N14))


Regards
Claus B.
--
Windows10
Office 2016

[dpb]

Fair enough complaint! :) I posted the function code at the end of the
"Many to One?" thread, but it is

Public Function SumSplitData(strText)
Dim re As Object
Dim ptrn, Tokens
Dim i As Integer
Dim SumSplitData As Double

Set re = CreateObject("vbscript.regexp")
' allow separators of semicolon, comma, blank, \n
ptrn = "[^;, " + Chr(10) + "]+"
re.Pattern = ptrn
re.IgnoreCase = False
re.Global = True
Set Tokens = re.Execute(strText)

SumSplitData = 0
For i = 0 To Tokens.Count - 1
SumSplitData = SumSplitData + CDbl(Tokens(i))
Next
End Function

If enter any values into A1 and A2, as (say)

A1: 600, <Alt-Enter> 236.54
A2: 1030.01, , <Alt-Enter> 200
A3: =SUM(A1:A2)

and B1, B2 contain =SumSplitData(A1), =SumSplitData(A2), then when
programatically via COM write "=SUM(B1:B2)" into B3, the formula in B3
is still text in the Excel spreadsheet while similarly haven written A3,
since column A is numeric instead and B is text, A3 is interpreted as
the formula and shows the number while B3 just shows the text.

As noted, can make any change like adding/deleting blank at beginning of
the formula and resave manually and then Excel converts the cell.

I've got another refinement to add so it'll be just a bit before can
test your idea, figured would post the added info now ...

--

[Claus Busch]

Hi,

try:

Function SplitData(strText As Range) As Double
Dim varTmp As Variant
Dim i As Integer

varTmp = Split(strText, Chr(10))
For i = LBound(varTmp) To UBound(varTmp)
If IsNumeric(Right(varTmp(i), 1)) Then
SplitData = SplitData + CDbl(varTmp(i))
Else
SplitData = SplitData + _
CDbl(Left(varTmp(i), Len(varTmp(i)) - 1))
End If
Next
End Function

[Claus Busch]

Hi again,

Am Thu, 17 Sep 2020 22:11:07 +0200 schrieb Claus Busch:

> try:
>
> Function SplitData(strText As Range) As Double
> Dim varTmp As Variant
> Dim i As Integer
>
> varTmp = Split(strText, Chr(10))
> For i = LBound(varTmp) To UBound(varTmp)
> If IsNumeric(Right(varTmp(i), 1)) Then
> SplitData = SplitData + CDbl(varTmp(i))
> Else
> SplitData = SplitData + _
> CDbl(Left(varTmp(i), Len(varTmp(i)) - 1))
> End If
> Next
> End Function

no matter what separators you have, you can search for integers and
decimals:

Public Function SumSplitData(strText As String) As Double

Dim re As Object
Dim ptrn, Tokens
Dim i As Integer

Set re = CreateObject("vbscript.regexp")

'looking for integers and decimals
ptrn = "\d+\.?\d*"

re.Pattern = ptrn
re.IgnoreCase = False
re.Global = True
Set Tokens = re.Execute(strText)

SumSplitData = 0
For i = 0 To Tokens.Count - 1
SumSplitData = SumSplitData + CDbl(Tokens(i))
Next
End Function

[dpb]

It's not this function that is the problem, Claus, it's the ordinary
summation in the cell below these that needs to sum the results of the
above cells, when written via COM is in a column that has to be text for
the <Alt+Enter> to work so that formula is then also interpreted as text
by Excel.

I'm trying to figure out how to change that cell containing the formula
=SUM(range) where range includes the 2:3 cells containing the results of
=SumSplitData().

It works just fine; the way it was coded is somewhat more flexible in
that it also let's them enter $ if want.

Anyways, it isn't where the problem is; the problem is of the other cell
being text first and Excel doesn't automagically change it back just
because of an '"' sign being first character in the string written to
the column cell.

--

[dpb]

On 9/17/2020 12:01 PM, dpb wrote:
> Almost got it!!!
>
> Everything working in putting the formulae in the right locations and
> works as desired if the column is numeric.
>
> The hiccup is the column that uses the Alt+Enter trick to wrap awards on
> successive lines in a cell has to be text so when write the summation
> formula for the group in the summary row for the particular fund it is
> interpreted as the text string of the sum.
>
> Can manually edit the string and then resave and Excel will then convert
> the cell and the sum is computed -- so the Q? now is,
>
> How to automate that process to not have to do that every time update
> the sheet?

...

Actually, don't even have to make any changes, just "F2 Enter" for each
cell will convert. If could do that programmatically would seem to
solve the problem...

--

[Claus Busch]

Hi,

Am Thu, 17 Sep 2020 17:49:19 -0500 schrieb dpb:

> Actually, don't even have to make any changes, just "F2 Enter" for each
> cell will convert. If could do that programmatically would seem to
> solve the problem...

the last posted function is more flexible and more reliable.
And the result of the function is alway a number formatted double.
Have a look:
https://1drv.ms/x/s!AqMiGBK2qniTge8fcVQb86srTXT87g?e=rXZzQW

[dpb]

It's NOT the problem...it's owing to the "wrap text" setting to display
the column of inputs with the line wrap _TO_ the function leaves the
column as a text column.

Then, when the COM engine writes the "=SUM(N12:N13)" formula into N14,
because that column is text the input is interpreted as text, not as the
formula.

It's fixing that that I'm looking to find a way to do w/o the manual "F2
<Enter>" exercise that does the trick.

That the sheet is being built via a COM connection from an external app
is a key component here...sure, if entered it all manually it would
behave as you describe--that's what's happening now if I do make the
fixup by hand or don't write the formulas externally, but enter them by
hand later.

The need is to be able to update frequently so if it takes 10-15 minutes
to clean the sheet up by hand again each time, that defeats the purpose.

--

[dpb]

On 9/17/2020 12:01 PM, dpb wrote:

NEVER MIND!!! Old man brain cramp -- trying to do the impossible; all
need to do is to carry over the sum from the target column of the
SumSplitData() call...

One could use the sum(SumSplitData(R1,R2,...RN)) but that's a lot more
messy than needs be.

Sorry for the wild goose chase...got off on the wrong track and couldn't
see forest for the trees until it finally just dawned on me what was
doing wrong.

--