seperating values from text
 

I have a cell that people can input both text and values in. Example:
Absent 15 Breaks 20. I need to be able to add the values in a seperate
cell. How would I do this?

seperating values from text
 

Data TextToColumns Space delimiter will separate the elements into
separate columns, then you can do the math.

Vaya con Dios,
Chuck, CABGx3




"enyaw" wrote:

I have a cell that people can input both text and values in. Example:
Absent 15 Breaks 20. I need to be able to add the values in a seperate
cell. How would I do this?

seperating values from text
 

This will not work as I need the sum to be automatic for the user, and I dont
want to change the layout of the worksheet.

"CLR" wrote:

Data TextToColumns Space delimiter will separate the elements into
separate columns, then you can do the math.

Vaya con Dios,
Chuck, CABGx3




"enyaw" wrote:

I have a cell that people can input both text and values in. Example:
Absent 15 Breaks 20. I need to be able to add the values in a seperate
cell. How would I do this?

seperating values from text
 

On Mon, 15 Jan 2007 05:13:02 -0800, enyaw
wrote:

I have a cell that people can input both text and values in. Example:
Absent 15 Breaks 20. I need to be able to add the values in a seperate
cell. How would I do this?

What is the variability in the data?

In other words, will the data always be
word/space/number/space/word/space/number?

Will the numbers always be integers?

Will there always be two numbers?

etc.


--ron

seperating values from text
 

enyaw wrote:
I have a cell that people can input both text and values in. Example:
Absent 15 Breaks 20. I need to be able to add the values in a seperate
cell. How would I do this?

will this UDF do?...

Public Function StringSum(cell As Range) As Single
Dim cellArray As Variant, I As Long
cellArray = Split(cell.Value)
For I = 0 To UBound(cellArray)
If IsNumeric(cellArray(I)) Then
StringSum = StringSum + cellArray(I)
End If
Next I
End Function

Ken Johnson

seperating values from text
 

There may be more than two items in the cell. The data will always be
word/space/number/space/word/space/number and the same for any more items
added.

"Ron Rosenfeld" wrote:

On Mon, 15 Jan 2007 05:13:02 -0800, enyaw
wrote:

I have a cell that people can input both text and values in. Example:
Absent 15 Breaks 20. I need to be able to add the values in a seperate
cell. How would I do this?

What is the variability in the data?

In other words, will the data always be
word/space/number/space/word/space/number?

Will the numbers always be integers?

Will there always be two numbers?

etc.


--ron

seperating values from text
 

Works cool in XP Ken, but I get an error in 97

Vaya con Dios,
Chuck, CABGx3


"Ken Johnson" wrote:

enyaw wrote:
I have a cell that people can input both text and values in. Example:
Absent 15 Breaks 20. I need to be able to add the values in a seperate
cell. How would I do this?

will this UDF do?...

Public Function StringSum(cell As Range) As Single
Dim cellArray As Variant, I As Long
cellArray = Split(cell.Value)
For I = 0 To UBound(cellArray)
If IsNumeric(cellArray(I)) Then
StringSum = StringSum + cellArray(I)
End If
Next I
End Function

Ken Johnson

seperating values from text
 

Works cool in XP Ken, but I get an error in 97

The code uses the Split function, which was added in 2000 (VBA6) and is not
available in 97 VBA5).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"CLR" wrote in message
...
Works cool in XP Ken, but I get an error in 97

Vaya con Dios,
Chuck, CABGx3


"Ken Johnson" wrote:

enyaw wrote:
I have a cell that people can input both text and values in. Example:
Absent 15 Breaks 20. I need to be able to add the values in a seperate
cell. How would I do this?

will this UDF do?...

Public Function StringSum(cell As Range) As Single
Dim cellArray As Variant, I As Long
cellArray = Split(cell.Value)
For I = 0 To UBound(cellArray)
If IsNumeric(cellArray(I)) Then
StringSum = StringSum + cellArray(I)
End If
Next I
End Function

Ken Johnson

seperating values from text
 

Hi Chuck,

Ron might know the answer to that.
I made it with XP and don't have 97.
I'll try it on my old iMac (OS 9.1 Office 2000) just to see what
happens there.

Ken Johnson

seperating values from text
 

I should have added, do a search on Groups.Google.com for "Split97" and
you'll find a function for VBA5 written Tom Ogilvy that reproduces the
functionality of the Split function but works in Excel 97.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Chip Pearson" wrote in message
...
Works cool in XP Ken, but I get an error in 97

The code uses the Split function, which was added in 2000 (VBA6) and is
not available in 97 VBA5).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"CLR" wrote in message
...
Works cool in XP Ken, but I get an error in 97

Vaya con Dios,
Chuck, CABGx3


"Ken Johnson" wrote:

enyaw wrote:
I have a cell that people can input both text and values in. Example:
Absent 15 Breaks 20. I need to be able to add the values in a
seperate
cell. How would I do this?

will this UDF do?...

Public Function StringSum(cell As Range) As Single
Dim cellArray As Variant, I As Long
cellArray = Split(cell.Value)
For I = 0 To UBound(cellArray)
If IsNumeric(cellArray(I)) Then
StringSum = StringSum + cellArray(I)
End If
Next I
End Function

Ken Johnson

seperating values from text
 

OIC.........thanks Chip

Vaya con Dios,
Chuck, CABGx3



"Chip Pearson" wrote:

Works cool in XP Ken, but I get an error in 97

The code uses the Split function, which was added in 2000 (VBA6) and is not
available in 97 VBA5).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"CLR" wrote in message
...
Works cool in XP Ken, but I get an error in 97

Vaya con Dios,
Chuck, CABGx3


"Ken Johnson" wrote:

enyaw wrote:
I have a cell that people can input both text and values in. Example:
Absent 15 Breaks 20. I need to be able to add the values in a seperate
cell. How would I do this?

will this UDF do?...

Public Function StringSum(cell As Range) As Single
Dim cellArray As Variant, I As Long
cellArray = Split(cell.Value)
For I = 0 To UBound(cellArray)
If IsNumeric(cellArray(I)) Then
StringSum = StringSum + cellArray(I)
End If
Next I
End Function

Ken Johnson

seperating values from text
 

Thanks Ken.............Chip jumped in and cleared it up.

Vaya con Dios,
Chuck, CABGx3



"Ken Johnson" wrote:


Hi Chuck,

Ron might know the answer to that.
I made it with XP and don't have 97.
I'll try it on my old iMac (OS 9.1 Office 2000) just to see what
happens there.

Ken Johnson

seperating values from text
 

Chip Pearson wrote:
Works cool in XP Ken, but I get an error in 97

The code uses the Split function, which was added in 2000 (VBA6) and is not
available in 97 VBA5).



Thanks Chip,

It's not on Office 2001 for Mac either.

Ken Johnson

seperating values from text
 

Hmmmm, must be a little mind-reading going on here somewhere........I was
just trying to work-up a post asking if that was possible..........

Thank you muchly, kind Sir.

Vaya con Dios,
Chuck, CABGx3



"Chip Pearson" wrote:

I should have added, do a search on Groups.Google.com for "Split97" and
you'll find a function for VBA5 written Tom Ogilvy that reproduces the
functionality of the Split function but works in Excel 97.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)


"Chip Pearson" wrote in message
...
Works cool in XP Ken, but I get an error in 97

The code uses the Split function, which was added in 2000 (VBA6) and is
not available in 97 VBA5).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

"CLR" wrote in message
...
Works cool in XP Ken, but I get an error in 97

Vaya con Dios,
Chuck, CABGx3


"Ken Johnson" wrote:

enyaw wrote:
I have a cell that people can input both text and values in. Example:
Absent 15 Breaks 20. I need to be able to add the values in a
seperate
cell. How would I do this?

will this UDF do?...

Public Function StringSum(cell As Range) As Single
Dim cellArray As Variant, I As Long
cellArray = Split(cell.Value)
For I = 0 To UBound(cellArray)
If IsNumeric(cellArray(I)) Then
StringSum = StringSum + cellArray(I)
End If
Next I
End Function

Ken Johnson

seperating values from text
 

Ken,

I think all versions of Mac Office still use VBA5, even the latest version.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)



"Ken Johnson" wrote in message
ups.com...

Chip Pearson wrote:
Works cool in XP Ken, but I get an error in 97

The code uses the Split function, which was added in 2000 (VBA6) and is
not
available in 97 VBA5).



Thanks Chip,

It's not on Office 2001 for Mac either.

Ken Johnson

seperating values from text
 

Chip Pearson wrote:
Ken,

I think all versions of Mac Office still use VBA5, even the latest version.


--

Yeah, I remembered that after posting, and I believe VBA5's as high as
it will ever go with Macs.

Ken Johnson

seperating values from text
 

On Mon, 15 Jan 2007 05:59:01 -0800, enyaw
wrote:

There may be more than two items in the cell. The data will always be
word/space/number/space/word/space/number and the same for any more items
added.

Here's one way, then.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then you can use one of these two formulas (I'm not sure which one is faster).
These formulas use Regular Expressions to extract the numeric values from the
strings.

They will extract all numbers.

If you might have a number in the "word" portion, that you wish to have
ignored, (e.g. wo23rd 658 wo5rd 123) and you want to ignore the 23 and the 5,
but add the 658 and 123, some further changes will be required in the "Regex".



The **array** formula:

=SUM(--REGEX.MID(A1,"\d+",INTVECTOR(REGEX.COUNT(A1,"\d+") ,1)))

(To enter an array formula, hold down <ctrl<shift when you hit <enter.
Excel will place braces {...} around the formula).

Or the non-array formula:

=EVAL(MCONCAT(REGEX.MID(A1,"\d+",INTVECTOR(REGEX.C OUNT(A1,"\d+"),1)),"+"))

You did not answer my question as to whether the numbers would be integers or
not. The above formula will work for integers. If the values may include
decimals, and/or be positive or negative, then instead of "\d+" you should
substitute the following:

"[-+]?(\d*\.)?\d+"

which would result in

**array entered**:

=SUM(--REGEX.MID(A1,"[-+]?(\d*\.)?\d+",
INTVECTOR(REGEX.COUNT(A1,"[-+]?(\d*\.)?\d+"),1)))

OR normally entered:

=EVAL(MCONCAT(REGEX.MID(A1,"[-+]?(\d*\.)?\d+",
INTVECTOR(REGEX.COUNT(A1,"[-+]?(\d*\.)?\d+"),1)),"+"))





--ron

seperating values from text
 

On Mon, 15 Jan 2007 10:15:10 -0500, Ron Rosenfeld
wrote:

On Mon, 15 Jan 2007 05:59:01 -0800, enyaw
wrote:

There may be more than two items in the cell. The data will always be
word/space/number/space/word/space/number and the same for any more items
added.

Here's one way, then.

Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr

Then you can use one of these two formulas (I'm not sure which one is faster).
These formulas use Regular Expressions to extract the numeric values from the
strings.

They will extract all numbers.

If you might have a number in the "word" portion, that you wish to have
ignored, (e.g. wo23rd 658 wo5rd 123) and you want to ignore the 23 and the 5,
but add the 658 and 123, some further changes will be required in the "Regex".



The **array** formula:

=SUM(--REGEX.MID(A1,"\d+",INTVECTOR(REGEX.COUNT(A1,"\d+") ,1)))

(To enter an array formula, hold down <ctrl<shift when you hit <enter.
Excel will place braces {...} around the formula).

Or the non-array formula:

=EVAL(MCONCAT(REGEX.MID(A1,"\d+",INTVECTOR(REGEX. COUNT(A1,"\d+"),1)),"+"))

You did not answer my question as to whether the numbers would be integers or
not. The above formula will work for integers. If the values may include
decimals, and/or be positive or negative, then instead of "\d+" you should
substitute the following:

"[-+]?(\d*\.)?\d+"

which would result in

**array entered**:

=SUM(--REGEX.MID(A1,"[-+]?(\d*\.)?\d+",
INTVECTOR(REGEX.COUNT(A1,"[-+]?(\d*\.)?\d+"),1)))

OR normally entered:

=EVAL(MCONCAT(REGEX.MID(A1,"[-+]?(\d*\.)?\d+",
INTVECTOR(REGEX.COUNT(A1,"[-+]?(\d*\.)?\d+"),1)),"+"))





--ron


And if numbers may be in the "word"s, then try this regex instead:

"[-+]?\b(\d*\.)?\d+\b"


--ron

seperating values from text
 

On Mon, 15 Jan 2007 10:31:45 -0500, Ron Rosenfeld
wrote:




And if numbers may be in the "word"s, then try this regex instead:

"[-+]?\b(\d*\.)?\d+\b"



I should amplify that the above regex will "ignore" numbers that are embedded
within words. The original variations will "include" those numbers.

--ron